topic Re: Filter date in Calculate function in DAX Commands and Tips

Filter date in Calculate function

Anonymous — Fri, 28 Jan 2022 21:35:58 GMT

Hello,

I am posting because I am pretty new on Power BI and I seem to be stuck on a particular issue I hope you could help me with.

The problem is the following :

I have a list of number, related to dates. It allows me to make table with value per month.

I would like to isolate the value of the last month with a code as below but it does not work and i do not understand why...

mesure december =

var MAXI = month(LASTDATE(Data[Date]))

return

CALCULATE(sum(Data[Value]) , month(Data[Date]) = MAXI)

Please let me know if it is understandable and thank you in advance for your help

Re: Filter date in Calculate function

ValtteriN — Sat, 29 Jan 2022 17:00:27 GMT

Hi,

For LM calculations I recommend using functions like PREVIOUSMONTH, DATEADD or PARARALLELPERIOD.

e.g. CALCULATE(sum(Data[Value]) , PREVIOUSMONTH('Calendar'[Date]))

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

Re: Filter date in Calculate function

Anonymous — Thu, 03 Feb 2022 03:10:54 GMT

Hi Anonymous ,

Could you please tell me what is your desired output？
I have created a simple sample.

And the formula works well.

mesure december = var MAXI = month(LASTDATE(Data[Date])) return CALCULATE(sum(Data[Value]) , month(Data[Date]) = MAXI)

If it is possible, please provide your pbix file without privacy information and desired output.

Best Regards

Community Support Team _ Polly

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

Re: Filter date in Calculate function

Anonymous — Wed, 09 Feb 2022 20:31:08 GMT

Hello,

Sorry for the late reply.

I will attache the related document do you know how to proceed ?

The issue I have with my calculation is the following :

The total on column 2 focus on last month and is ok (310). But i don't understand why the others month that should be filter appear in the list (january to november)

Re: Filter date in Calculate function

Whitewater100 — Wed, 09 Feb 2022 21:59:32 GMT

Hi:

If you want to make a measure Total Amt = SUM(Data[Vlue])

Then you can do:

Last Month := CALCULATE([Total Amt], PREVIOUSMONTH('Dates'[Date])) or

Last Month = CALCULATE(SUM(Data[Value]), PREVIOUSMONTH('Dates'[Date]))

This should be OK for you.