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] ) -- retrieve latest date

VAR MinDate = EDATE( MaxDate, -12 ) -- move it back 12 months

VAR Result =

CALCULATE(

'Actuals_by_Month'[Actuals_Volume],

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

why it just show value for the selected month? why it is not rolling back 12 months?

Need Help

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!

17 Replies

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
  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!
Mikelytics
Resident Rockstar
3 years ago
Anonymous
Weird. I think I now set it up exactly like you. I added a LastDayOfMonthColum in my data table and adjsuted the formula by refering to the new column and it works

Date Table

Formula

Last 12 months v4 = VAR MaxDate = MAX( Dim_Date[LastDayOfMonth] ) -- retrieve latest date VAR MinDate = EDATE( MaxDate, -12 ) -- move it back 12 months VAR Result = CALCULATE( [Aggregation | Sum], FILTER( ALL( Dim_Date ), -- return period between Dim_Date[LastDayOfMonth] <= MaxDate && -- latest date Dim_Date[LastDayOfMonth] > MinDate ) -- bigger than year before ) RETURN Result

Result

What is about your data model. Which Columns are connected between dimension and fact? What is the relation type and what the filter direction? Can you show a screenshpt please?

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
  Not applicable
  3 years ago
  my data model is pretty simple but the fact table is joined with few dimensions wiht one- to- many. however the re;lationship between calendar and fact table is defined by a relationship as screenshot
  - Mikelytics
    Resident Rockstar
    3 years ago
    Hi Anonymous
    
    it looks totally the same on my side. But there is one thing I observed. Please look on the screenshot what I highlighted
    is this a measure? or a column reference?
    normally it should not give something back but is it possible that you need to ^replace it by
    SUM('Actuals_by_Month'[Actuals_Volume]) ??
    
    Can you pleae show the measure definition you have?
    
    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.