cancel
Showing results for
Did you mean:
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.

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

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

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

3 REPLIES 3
Microsoft

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

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.

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

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

Proud to be a Super User!

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

Proud to be a Super User!

Announcements

#### Happy New Year from Power BI

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