Forum Discussion
Column Table Measure to Show Individual Values
I have two datasets i am trying to show in a table/column format. However, I can't get the second dataset values to show indivdual Forecast values as it rolls them up into TOTAL values throughout the months.
Both have realtionship links to a date table, however it is still rolling them up. The main function is using the filter to showcase specifc months using the slicer. See below link to file, image and measure.
The forecast measure below is the same as the sales measure. However, is looking at "Forecast" Values.
Forecast (last n months) =
VAR MaxFactDate =
CALCULATE ( MAX ( Sales[Date] ), ALL ( 'Date' ) ) -- ignore the selected date filter, and find the max of date in Forecast table
VAR FDate =
ENDOFMONTH ( 'Date'[Date] ) -- get the last day of the month selected in the date filter
VAR Edate =
EDATE ( FDate, -[N Value] ) -- get the last day of -N months
RETURN
IF (
MaxFactDate <= MAX ( 'Date'[Date] )
&& MaxFactDate > Edate,
CALCULATE ( SUM ( Forecast[Forecast] ), ALL ( 'Date' ) )
)-- if the date in the fact table is between the last N months, display Forecast, else nothing. Note that we are ignoring the date filter, only respect the date in FACT
is there a way make it so the forecast values are not Total values?
File data shared link below
2 Replies
HI Anonymous,
It seems like you are summary forecast table records, I'd like to suggest you add a condition to check the current date and use it to limit the calculation ranges:
Forecast (last n months) = VAR currDate = MAX ( 'Date'[Date] ) VAR MaxFactDate = CALCULATE ( MAX ( Sales[Date] ), ALL ( 'Date' ) ) VAR Edate = EDATE ( ENDOFMONTH ( 'Date'[Date] ), - [N Value] ) -- get the last day of -N months RETURN IF ( MaxFactDate <= currDate && MaxFactDate > Edate, CALCULATE ( SUM ( Forecast[Forecast] ), FILTER ( ALLSELECTED ( 'Forecast' ), 'Forecast'[Date] <= currDate ) ) )Regards,
Xiaoxin Sheng