cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Morizot
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)
 
Please let me know if it is understandable and thank you in advance for your help
4 REPLIES 4
Whitewater100
Solution Sage
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.

v-polly-msft
Community Support
Community Support

Hi @Morizot ,

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)

vpollymsft_0-1643857767702.png

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.

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)

 

Morizot_0-1644438318744.png

 

 

ValtteriN
Super User
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!

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





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

Proud to be a Super User!




Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

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

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

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

Power BI Dev Camp Session 22 without aka link and time 768x460.jpg

Check it Out!

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

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!