Forum Discussion

mrklucz's avatar
mrklucz
Regular Visitor
3 years ago
Solved

Relative time measures with Dynamic Time axis

Hi,   I'm having an issue with a measure with relative time period.   My model is having dateTable and factTable with a relationship.   My measure looks like: Measure= VAR CurrentMonth = ...
  • mrklucz's avatar
    mrklucz
    3 years ago

    amitchandak it was not an option since I'm using a live connection to AAS, but I managed to resolve this issue. 

     

    I created a table 'DatesWithPartsYear' that combines dates, DisplayDates, PartOfYear as a seperate table (no connection to the model) and then re-caluclated my measure for this report to.

     

    In DateTable I created additional columns "Year Month" and "Year Quarter" - to follow the same data schema as in DatesWithPartsYear. then in my measers I added TREATAS - to treat my displayDate as a specific column from ch_date depends on context ot PartYear.

     

     

     

    Actual = 
    SWITCH(
        VALUES ( DatesWithPartsYear[PartYear] ),
        "Month",
            CALCULATE(
                SUM(factTable[Value]),
                factTable[MeasureTypeName] = "Actual",
                TREATAS ( VALUES ( v_DatesWithPartsYear[DisplayDate] ) , dateTable[Year Month])
            ),
        "Quarter",
            CALCULATE(
                SUM(factTable[Value]),
                factTable[MeasureTypeName] = "Actual",
                TREATAS ( VALUES ( DatesWithPartsYear[DisplayDate] ) , dateTable[Year Quarter])
            )
        )