Forum Discussion

StephenF's avatar
StephenF
Responsive 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
Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hi StephenF ,

For your scenario, you can create variable to store current column label, then convert it to date and calculate out previous date.
After these steps, you can simply extract year and month part as filter conditions for calculate:

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

If you still confused on coding formula, can you please share some sample data for test?

Regards,

Xiaoxin Sheng

StephenF's avatar
StephenF
Responsive 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