cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper III
Helper III

Help with last month, last two months and sum of last three months

Hello all!!

 

I have this issue, and I dont know what is wrong in the dax,

I have this matrix with this format.

lastmonth.JPG

Im using this formulas to calculate the last month selected and the last two months select

Actual Last Month = CALCULATE(SUM('Table'[Amount]),DATEADD('Date'[Date],-1,MONTH))
Actual Last 2 Month = CALCULATE(SUM('Table'[Amount]),DATEADD('Date'[Date],-2,MONTH))
 
For example If select all the months, with Actual Last Month, I hope to obtain the value of december
and with actual last 2 Month, i hope to obtain the value of november.
And I recieved another values.
 
With the formula 
Actual Last 3 Month = CALCULATE(SUM('Table'[Amount]),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-3,MONTH))
I hope to obtain the sum of the last 3 months, I think is the only correct, but im not sure about it.
 
 
I hope someone could help me with this issue.
 
Thanks a lot.
Monica
1 ACCEPTED SOLUTION
Microsoft
Microsoft

Hi @MonicaElizondo ,

 

According to your description, you want to show value for december as last month , show value for november as last 2 month and show value for october , december and november as last 3 month when you select all values in month name slicer ,am I right?

 

Just use the following measure depend on  measure in your pbix file:

 

Amount Actual = SWITCH(TRUE(),[Selection Measure]=1,[January],[Selection Measure]=2,[February],[Selection Measure]=3,[March],[Selection Measure]=4,[April],[Selection Measure]=5,[May],[Selection Measure]=6,[June], [Selection Measure]=7,[July], [Selection Measure]=8,[August], [Selection Measure]=9,[September],[Selection Measure]=10,[October], [Selection Measure]=11,[November],[Selection Measure]=12,[December],[Selection Measure]=13,IF(HASONEVALUE('Date'[Month Name]),[Actual Last Month],[December]),[Selection Measure]=14,[Amount YTD], [Selection Measure]=15,IF(HASONEVALUE('Date'[Month Name]),[Actual Last 2 Month],[November]),[Selection Measure]=16,[Actual Last 3 Month])

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Best Regards,

Dedmon Dai

 

View solution in original post

3 REPLIES 3
Microsoft
Microsoft

Hi @MonicaElizondo ,

 

According to your description, you want to show value for december as last month , show value for november as last 2 month and show value for october , december and november as last 3 month when you select all values in month name slicer ,am I right?

 

Just use the following measure depend on  measure in your pbix file:

 

Amount Actual = SWITCH(TRUE(),[Selection Measure]=1,[January],[Selection Measure]=2,[February],[Selection Measure]=3,[March],[Selection Measure]=4,[April],[Selection Measure]=5,[May],[Selection Measure]=6,[June], [Selection Measure]=7,[July], [Selection Measure]=8,[August], [Selection Measure]=9,[September],[Selection Measure]=10,[October], [Selection Measure]=11,[November],[Selection Measure]=12,[December],[Selection Measure]=13,IF(HASONEVALUE('Date'[Month Name]),[Actual Last Month],[December]),[Selection Measure]=14,[Amount YTD], [Selection Measure]=15,IF(HASONEVALUE('Date'[Month Name]),[Actual Last 2 Month],[November]),[Selection Measure]=16,[Actual Last 3 Month])

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Best Regards,

Dedmon Dai

 

View solution in original post

Super User IV
Super User IV

@MonicaElizondo - These may help. I often find that it is better to be explicit about my filtering rather than rely on a generally imperfect understanding of the black boxes that are the time "intelligence" functions.

 

You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000

Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

 

DATEADD is a particularly nasty function, there's a reason I saved it until last.


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Super User IV
Super User IV

@MonicaElizondo , these formula seems correct. But seem like you have there can be a problem with what do get this kind of display. Please reconsider that.

As there is a disconnected table that can change the numbers.

Actual the measure name table is creating a cartesian with your time intelligence measure. They seem correct to me.

 

Refer the page 2 of the attached file.

You are looking for a hybrid table, I logged and idea for that

https://ideas.powerbi.com/ideas/idea/?ideaid=9bc32b23-1eb1-4e74-8b34-349887b37ebc

 

Power BI — MTD
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors