DAX Formulas for 1/2 Year Running Totals in Power BI or Power Pivot (Excel Magic Trick 1561)

Download Excel File: excelisfun.net...
Download Start Power BI Desktop: excelisfun.net...
Download Finished Power BI Desktop File: excelisfun.net...
Entire page with all Excel Files for All Videos: excelisfun.net...
Learn how to create a DAX Calculated Colum and DAX Measure for Half Year Running Totals in Power BI Desktop or Excel Power Pivot.
Topics:
1. (00:01) Introduction
2. (00:20) Files to download
3. (00:51) Overview of Data Model
4. (01:05) DAX Calculated Column for Half Year Date Table Attribute
5. (02:13) DAX & Excel Worksheet Formulas can mix Data Types - this is different than Power Query
6. (03:00) What is Filter Context?
7. (04:30) DAX Measure for Running Total using FILTER, ALL, MAX, VALUES and CALCULATE DAX Functions
8. (09:28) Format DAX Measures
9. (10:30) Conclusion

[@excelisfun]

Topics: 1. (00:01) Introduction 2. (00:20) Files to download 3. (00:51) Overview of Data Model 4. (01:05) DAX Calculated Column for Half Year Date Table Attribute 5. (02:13) DAX & Excel Worksheet Formulas can mix Data Types - this is different than Power Query 6. (03:00) What is Filter Context? 7. (04:30) DAX Measure for Running Total using FILTER, ALL, MAX, VALUES and CALCULATE DAX Functions 8. (09:28) Format DAX Measures 9. (10:30) Conclusion

[@GeertDelmulle]

Thanks Mike, Nice little video! This measure really allows people to understand many aspects of the DAX philosophy: - the FILTER()-function filter argument is responsible for the “Running Total” aspect: * the innermost context inside that FILTER()-function has its own row context (of course), * inside that context the aggregator MAX modifies the context of its (!) argument by ignoring the (local) row context and pulling in the external filter context. It ‘even’ calculates the max (of course!) of its argument in case of multiple values, which is the case for the Total rows in the external report context, which is crucial for the resulting measure to work on those report lines as well. - the VALUES()-function filter argument acts like a “Selector” (based on what you put in as its function argument). It is crucial to understand that both these filter arguments must have an external filter context in order to work their magic. It is also important to realize that both these filter arguments are table filters (they both produce tables as their outputs). Now let’s take this one step further: Context: imagine you have data for many years and your core measure is [Total Sales]. Imagine you want to calculate the maximum value of monthly sales over the year-period. Imagine you want to plot those max-values vs. time (over many years). What do we see, then? Well, in that case you can’t use/recycle this exact measure you just created - it wouldn’t work... Why?

[@chawkibouderbala9418]

Absolute Genius , it also works with power bi !!! Simple and so efficient !! One thousand thanks for sharing this video

[@excelisfun]

You are welcome!

[@chrism9037]

Very impressive Mike!

[@excelisfun]

Thank you, Teammate Chris : )

[@lucasfarianogueira]

Great explanation. Even if I don't need this kind of calculation now, I got the knowledge. Thanks for sharing

[@excelisfun]

You are welcome!!

[@MalinaC]

OMG! Amazing! Running totals - extremely important!

[@excelisfun]

Yes!!!!

[@wajdimatoussi]

Hello Mike Now it's 2 am o'clock in France and with this video I can say you made my night :) Many thanks for this great video..

[@excelisfun]

Yes!!!! 2 AM, Make The Night : ) Many you are welcomes for the video, Wajdi!!!

[@wayneedmondson1065]

Great Mike.. another super lesson of DAX formula fun. Thumbs up!

[@excelisfun]

Thanks, Wayne !!!!

[@jazzista1967]

Hi Mike. Excelent Video as always! I tried the M code for the half year formula and I think that it should be written as follows: Number.ToText(Date.Year([Date]))&"-"&Number.ToText(if Date.Month([Date])

[@excelisfun]

Yes, you are right, I forgot that the Boolen formula I created in DAX did not directly translate to M Code. We could use this too: Number.ToText(Date.Year([Date])) &" - "& Number.ToText(if Date.Month([Date])

[@jazzista1967]

Thanks to you mike for posting these awesome videos and all the effort you put while making them!

[@excelisfun]

@@jazzista1967 You are welcome, jazzista1967 : ) Thanks for your help : )

[@sahilsinghal9472]

Very very helpful thank you so much

[@excelisfun]

You are welcome so much, Sahel! Thanks for your support with your comment, thumbs up and your Sub : )

[@joad_lee]

This video makes me think about custom calendars. With custom calendars, we can't rely on easy time-intelligence DAX because those functions are based on a regular calendar. As New Year's Day approaches a new decade, imagine how time-intelligence would work in a lunar calendar.

[@excelisfun]

I do not know how the lunar calendar would work in DAX! Although my wife and my Mom follow the lunar calendar, i do not. I guess Bill Gates will have to invent some new DAX CALENDAR.LUNAR functions ; )

[@SyedMuzammilMahasanShahi]

Beautiful video Mike. Thanks for the share.

[@excelisfun]

You are welcome for the share, Syed : ) : )

[@EricaDyson]

Great. I just used the IF IsFiltered for my cumulative totals. Just what I needed. Thanks so much.

[@DougHExcel]

Great video!

[@excelisfun]

Thanks, Doug!!!

[@ljubicar1987]

Thanks for the video! By the way, I prefer typing a Dax formula in line, rather than doing the formatting. (You mentioned in some video to leave a comment on what we like better.) Formatting is more confusing to me than helpful.

[@excelisfun]

You and I are in the minority. Most people like the Formatting...

[@vida1719]

Thanks for DAX fun

[@excelisfun]

You are welcome for the DAXisFun!!!

[@davideferrari9333]

Hello Mike!! Thank you very much for this video! Can you please explain in details how the row and internal and external filter context work for the two dDate[Date] in the FILTER function? Thank you in advance

[@ogwalfrancis]

Thanks You Very Much Mike

[@excelisfun]

You are very welcome, Ogwal!!!

[@johnborg6005]

Thanks mike !!!!

[@excelisfun]

You are welcome, John Borg!

[@DIGITAL_COOKING]

short and smart !!! :)

[@excelisfun]

Fun too : )

[@DIGITAL_COOKING]

@@excelisfun yes!, indeed 😄👍

[@harishpaldhir5915]

Good one sir 😈😈😈

[@excelisfun]

Glad it is a good one for you, HARISH!!!!

[@15071982]

Thanks Sir. Shall we use ALLSELECTED as well instead of ALL. If yes what is the difference between ALL and ALLSELECTED?

[@excelisfun]

The true meaning of ALLSELECTED is complicated. But no worries, I have told the complete story. If you really want to know here are the links for the video and pdf notes: Here is a video with everything about ALLSELECTED: kzbin.info/www/bejne/rYK3ZKmYg9V4isU here is the link for the pdf notes: people.highline.edu/mgirvin/AllClasses/348/MSPTDA/Content/DAX/019-MSPTDA-CALCULATE-FilterContext.pdf From page 20 in pdf notes: What ALLSELECTED does: Removes the last filter generated from Context Transition and jumps back to the Previous Filter Most of the Time, ALLSELECTED() is used to get a Denominator Number for % of Grand Totals and allow Filter or Slicer selections to influence the Grand Total. iii. A formula like this: CALCULATE([Total Sales],ALLSELECTED()) ALLSELECTED would remove the last filter (Row Filter) and jump back to the Row/Column/Filter/Slicer filtering for the Grand Total cell in the External Filter Context in the Excel PivotTable or the Power BI Visual. This is helpful if you would like your Measure to calculate the Grand Overall Total based on any applied Filters by the client (end report consumer). As seen in the picture on the next page, ALLSELECETED() instructed CALCULATE to remove the “Quad” Row Context and jump back to the Total Revenue Grand Total Filter Context which are the filters “Carlota”, “Quad”, and Sunshine” from the dBoomProduct table and the “West” Region from the dSalesRep table as seen here.... see rest on pdf notes page 20.

[@EricaDyson]

Fun?! Well I followed you (just about), but could I repeat it on my own applied to something else? Hmm.. got to give a try!

[@excelisfun]

Of course you can !!!!! : ) : ) But... the way we humans learn and apply new things has not changed in 10,000 years: practice, practice, practice, try, mistake, try, mistake, try, mistake... DAX is beautiful but complicated. But with the MSPTDA class here at excelisfun, at least there is a way for you to study, learn, practice : )

[@EricaDyson]

@@excelisfun I'm good at the 'try/mistake' part :-). Seriously, my DAX is coming along nicely ... with your assistance!

[@excelisfun]

@@EricaDyson I think that is the ONLY why that I have really learning anything in life: try/mistake... I am glad to hear that your DAX is coming along. Do you make your DAX Formulas in Excel Power Pivot, Power BI or other? And what sort of formulas do you make?

[@EricaDyson]

@@excelisfun Well I started with Power BI then bought some books and realized that I wanted to start with Excel (the core). That was a good move. So now I use both. I'm quite pleased with the time-based measures with blanks when there is no previous year data and that sort of stuff. Doing nicely with FILTER and getting going with measures using variables ie doing the whole thing in one measure rather than splitting them. So nothing particularly startling I suppose but compared to the data management and reporting that I did years ago, Power Pivot and BI and just amazing. And it's fun..yes , I love it.

[@excelisfun]

@@EricaDyson I love to hear that! You are doing a lot with DAX!!!! What sort of reports do you create?