DAX: Show last two months breakdown dynamically

[Anonymous]

Hi guys

I am stuck with a DAX problem. I have users selecting a month and based on that selection I want to dynamically show the breakdown between the previous and selected month in terms of decrease/increase etc. with a waterfall chart, something like this:

I am aware that I have to use an independent Date to be used in the charts as otherwise, the concept would not work. I have an IdDay and a date Tons_Date in the fact table that I use.

I wrote the following measure:

Previous 2 Months Actual Sales= 
VAR _MaxFactDate = CALCULATE(MAX('Sales'[IdDay]);ALL('DATE_Day'))
VAR _Tons = [Actual Sales]
VAR _maxDate = ENDOFMONTH('DATE_Day'[Date])
VAR _minDate = LOOKUPVALUE('DATE_Day'[IdDay];'DATE_Day'[Date];EDATE(_maxDate;-2))

RETURN

IF(_MaxFactDate <= MAX('DATE_Day'[IdDay]) && _MaxFactDate > _minDate;
    CALCULATE([Actual Sales];ALL('DATE_Day')))

where Date_Day is my main Date Table (and use in the slicer) and [Actual Tons] the explicit measure for which I want to show the breakdown.

The measure sort of works in a way that it shows the previous two months dynamically, except for September (it goes back to July although it should only show August and July), and unfortunately it does not show the right value for [Actual Tons].

Can anyone see a mistake in the DAX above? 

Thanks a lot for the help!

[amitchandak · Accepted solution]

Try Below. But you need to have Date table for that

Rolling 2 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date Filer],ENDOFMONTH(Sales[Sales Date]),-2,MONTH))  

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s.

Refer
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions

https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[amitchandak · Accepted solution]

Try Below. But you need to have Date table for that

Rolling 2 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date Filer],ENDOFMONTH(Sales[Sales Date]),-2,MONTH))  

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s.

Refer
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions

https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[Anonymous · Accepted solution]

@amitchandak ha!
I don't know how I got lost in all that complexity in my previous formula but you made me realize that this can be achieved much simpler. Your formula did not work entirely as it was missing an ALL statement at the end and actually the date in the fact table is not needed in the formula, only in the chart itself. But here's the working one:

Rolling 2 = CALCULATE([Actual Sales];DATESINPERIOD('DATE_Day'[Date];ENDOFMONTH('DATE_Day'[Date]);-2;MONTH);ALL('DATE_Day'))  

Thanks for pointing me in the right direction, I was very lost as it seems 😄 

[kentyler]

Can you post a small sample power bi file ? Helps a lot with this kind of debugging.

[Anonymous]

Hi @kentyler, unfortunately, I can't share the file due to the sensitivity of the information which means I would have to build a separate one, with fake data... I was hoping maybe someone would be able to spot a mistake in the logic of the DAX. If not, you are absolutely right, then I will go down the road of creating a sample file 🙂