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!

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Mikelytics , ok let me try to remove stuffs and just keep the calendar and fact table. it might take 10 or 15 mins. I will get back to you . thanks so much!

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Mikelytics I created a new sample pbix project and kept just the fact table and calendar table. calendar(Date) joined to fact table(last day of the month). My visual looks like this

I changed my measure like below

Last 12 months v4 =
VAR MaxDate = MAX( 'Calendar'[Calendar_last_day_of_month] ) -- retrieve latest date
VAR MinDate = EDATE( MaxDate, -12 ) -- move it back 12 months
VAR Result =
CALCULATE(
sum(Actuals_by_Month[AMOUNT]),0)
FILTER( ALL( 'Calendar' ), -- return period between
'Calendar'[Calendar_last_day_of_month] <= MaxDate && -- latest date
'Calendar'[Calendar_last_day_of_month] > MinDate ) -- bigger than year before
)
RETURN
Result

 

Not sure why it is still showing like this. just to confirm the "calendar last day of month" should be pulled from calendar for both canvas and slicer. correct?

 

  • Mikelytics's avatar
    Mikelytics
    Resident Rockstar
    3 years ago

    Hi Anonymous 

     

    so joining Calendar[Date] with 1:n relation to FactTable['[Calendar_last_day_of_month] is good.

     

    Can you please try to refer to the calendar key column in the measure instead?

    Last 12 months v4 =
    VAR MaxDate = MAX( 'Calendar'[Date] ) -- retrieve latest date
    VAR MinDate = EDATE( MaxDate, -12 ) -- move it back 12 months
    VAR Result =
    CALCULATE(
    sum(Actuals_by_Month[AMOUNT]),0)
    FILTER( ALL( 'Calendar' ), -- return period between
    'Calendar'[Date] <= MaxDate && -- latest date
    'Calendar'[Date] > MinDate ) -- bigger than year before
    )
    RETURN
    Result

  • Mikelytics's avatar
    Mikelytics
    Resident 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 , tried ,, but no luck 😞 

  • 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!