Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Last 12 months showing same value

Hi, I am trying to calculate last 12 months of data based on slicer "last day of month" . the code I used is below  Last 12 months v4 = VAR MaxDate = MAX( 'Calendar'[Calendar_last_day_of_month] )...
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Mikelytics , I was able to make this work but by different method - "USERELATIONSHIP". Here is the code: 

    _previous_amount =
    var ReferenceDate = MAX('Calendar'[Calendar_last_day_of_month])
    VAR PreviousDates =
    DATESINPERIOD('Previous Date'[Calendar_last_day_of_month],ReferenceDate, -13, MONTH)
    var result =
    CALCULATE(sum(ACTUALS_BY_MONTH[AMOUNT]),
    REMOVEFILTERS('Calendar'),
    KEEPFILTERS(PreviousDates) ,USERELATIONSHIP('Calendar'[Date], 'Previous Date'[Date]))
    RETURN
    result

    *************

    Previous Date ='Calendar'

     

    Results:

    Here is Youtube reference that I followed 

    https://www.youtube.com/watch?v=d8Rm7dwM6gc 

    Thanks for all of your suggestions and time!

Mikelytics's avatar
Mikelytics
Resident Rockstar
3 years ago

Hi Anonymous 

This is really weird. I tried out your measure and it worked on my side. Can please show your data model and how you set up the canvas? Especially how looks your calendar table like?

 

Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Appreciate your thumbs up!
@ me in replies or I'll lose your thread.

 

 

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Mikelytics I am generating Calendar like below 

    Calendar = CALENDARAUTO()
    please see the sreenshot of calendar table

     


     

     and this is how canvas is been set up. "CY Actuals Through" is "calendar_last_day_of_month" from 'Calendar'.
     

     

    Please let me know if you need any other information.

    thanks!