Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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

  • ValtteriN's avatar
    ValtteriN
    Community Champion
    4 years ago

    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/

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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)

       

       

       

  • Whitewater100's avatar
    Whitewater100
    Solution Sage
    4 years ago

    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.