Last 12 months showing same value

[priyanath1988]

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?

[Mikelytics]

@priyanath1988 

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.

[priyanath1988]

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]

Hi @priyanath1988 

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.

[priyanath1988]

@Mikelytics , Actuals_Volume is a measure defined as below

Actuals_Volume = COALESCE(sum(Actuals_by_Month[AMOUNT]),0)

[Mikelytics]

Have never seen COALESCE 😄

Did you try taking it out on only use SUM?

sum(Actuals_by_Month[AMOUNT])

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.

[priyanath1988]

@Mikelytics yeah, tried just the sum without COALESCE. No luck 😓

[Mikelytics]

 @priyanath1988 

hmm

Is the field you put into the slicer from the calendar table or from the fact table?

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.

[priyanath1988]

@Mikelytics  from Calendar.

[Mikelytics]

@priyanath1988 

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.

[priyanath1988]

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

[priyanath1988]

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

[priyanath1988]

@Mikelytics , tried ,, but no luck 😞 

[Mikelytics]

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

[priyanath1988 · Accepted solution]

@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]

Hi @priyanath1988 

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]

Hi @priyanath1988 

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.

[priyanath1988]

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