Forum Discussion

StephenF's avatar
StephenF
Icon for Responsive Resident rankResponsive Resident
7 years ago

Time sensitive measures for tables

I'm making a table where i want to measure a few things against a chosen dimension.   Now the below mockup can be partially done with a matrix but without the last 2 columns.   For a table I woul...
Need Help
StephenF's avatar
StephenF
Icon for Responsive Resident rankResponsive Resident
7 years ago

Thanks, This approach fails in the case where there is no value in a month for a particular dimension. 

 

Say the Dimension Blue has no value for Last month but does have a value for the month before that. It will then get the value for currentmonth-3.

 

So I guess a test needs to be done to see if a value is available and if not return zero also or,

 

Last Month is always=  year(TODAY()100)*100 +MONTH(today()) might be better.
Anonymous's avatar
Anonymous
Not applicable
7 years ago

HI StephenF ,

According to your description, it sounds like your date dimension not continuous. If this is a case, I think you can modify 'previous date' variable to find out last date based on current date.

Measure =
VAR currDate =
    MAX ( Table[YearMonth] )
VAR temp =
    CALCULATE (
        MAX ( Table[YearMonth] ),
        FILTER ( ALLSELECTED ( Table ), [YearMonth] < currDate )
    )
VAR prevDate =
    DATE ( LEFT ( temp, 4 ), RIGHT ( temp, 2 ), 1 )
RETURN
    CALCULATE (
        SUM ( Table[Amount] ),
        FILTER (
            ALLSELECTED ( Table ),
            [YearMonth]
                = YEAR ( prevDate ) * 100
                    + MONTH ( prevDate )
        ),
        VALUES ( Table[Type] )
    )

Regards,

Xiaoxin Sheng