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 would need to know how to create a measure that has is segmented towards Month-1, Month -2 Month-3 etc for each of the column headers. then the last last column would be simply (Month-1/Month-13)-1 or whatever.

 

Whats the formulas functions to achieve this? The quick measure forumula only works when your y dimension is a date.

 

Need Help

3 Replies

  • 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
      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