cancel
Showing results for
Did you mean:
New Member

Filter date in Calculate function

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)

4 REPLIES 4
Solution Sage

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.

Community Support

Hi @Morizot ,

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.

New Member

Hello,

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)

Super User

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!

Proud to be a Super User!

Announcements

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through June10th!

Check it Out!

Watch Session 22 Ted's Dev Camp along with past sessions!