topic Column Table Measure to Show Individual Values in Developer

Column Table Measure to Show Individual Values

Anonymous — Sat, 09 Jul 2022 08:06:46 GMT

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

Tablechart.pbi

Re: Column Table Measure to Show Individual Values

Anonymous — Tue, 12 Jul 2022 00:48:54 GMT

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

Re: Column Table Measure to Show Individual Values

Anonymous — Wed, 13 Jul 2022 12:42:29 GMT

Hi @Anonymous

The measure you suggested continues to roll up the values into totals. Is there a way to look at the "date" table to breakdown values into period monthly values?