Last Value Shows Total in Measure

[ntduong]

Hi Everyone,

I have an issue that I can't seem to solve. I have a measure that shows a cumulative sum for the last 12 months that works correctly except the value for the most recent transaction shows a grand total and not the expected value. I have no idea why this is happening since all the other numbers are correct on any of the other entry.

Here's an example of what I'm seeing when looking at my measure in a matrix chart:

Date	Qty	Units R12
5/6/21	8	2948
5/7/211	9	2954
5/8/21	1	2943
5/10/21	11	7117(Expected Value 2924)

This issue seems to stem to all levels in the matrix chart view from the latest year, latest month and latest day which all shows the total (7117) value. Does any one know what is causing this? I'm using this measure is a line chart and this issue basically causes the chart to jump dramatically on the last value. Any help or insight on this would be appreciated.

Here's the measure that I am using:

Units R12 =

Var SelectedMaxDate = MAX ( 'New Units'[Start Date] )

Var MinDate =

CALCULATE (

MIN ([Start Date]),

FILTER (

ALL('New Units'),

DATEADD (

'New Units'[Start Date],

12,

MONTH

) >= SelectedMaxDate

)

)

Return

CALCULATE (

SUM([Qty]),

ALL( 'New Units' ),

'New Units'[Start Date] <= SelectedMaxDate,

'New Units'[Start Date] > MinDate

)

[amitchandak · Accepted solution]

@ntduong , rolling should show last value in grand total

Try a measure with help from date table like

Rolling 12 = CALCULATE(sum(Table[Qty]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[ntduong]

Thanks!

I created a Date table with associations and this measure works. Had to rebuild a lot of my reports though to use the new date table but at least it all works now.