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
Icon for Resident Rockstar rankResident Rockstar
3 years ago

This is so wird because it worked properly on my side as I showed ouy in the beginning. I really hate to say it but I have a lack of ideas left. amitchandak Any idea? 😄

 

Anonymous's avatar
Anonymous
Not applicable
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!