Last 12 months showing same value

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

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? 😄

Mikelytics  from Calendar.

Anonymous 

Ok, so slowly I reach the limit of ideas ^^

You mentioned you have other dimensions connected to the fact table. 

so question 1) is there maybe another filter which is active from another dimension which causes the issue?

questions 2) is there ,maybe by accident, antother relation from another table to the calendar table, maybe auto-connected by Power BI? Can you please show the overview of relations as wel as if any of this table have an active filter? if there is any try to turn them of in the report.

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.

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!

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 , tried ,, but no luck 😞