Forum Discussion

ntduong's avatar
ntduong
Regular Visitor
5 years ago
Solved

Last Value Shows Total in Measure

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:

 

DateQtyUnits R12
5/6/2182948
5/7/21192954
5/8/2112943
5/10/21117117(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
)
Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    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.

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    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's avatar
      ntduong
      Regular Visitor
      5 years ago

      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.