Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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...
Need Help
Anonymous's avatar
Anonymous
Not applicable
4 years ago

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

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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?