Forum Discussion

HOrakova's avatar
HOrakova
Helper I
1 year ago
Solved

Diff between selected and previous period

Hi Experts, I've closed the previous one as solved, but when I add all filter the formula is not working again. So: I have [Date] column with only start of the week dates in it. I need to calculate...
Need Help
Show and Tell
Tips and Tricks
  • HOrakova's avatar
    HOrakova
    1 year ago

    Hi v-kpoloju-msft  and techies ,

     

    I managed to get the measure to work the way I needed it to:

    Previous Period Value =
    VAR PeriodType = MAX('Period'[Period Type])  
    VAR PeriodsBack = SELECTEDVALUE('No of Periods'[Value])
    VAR PrevRank = [Switch MAX Period Rank] - PeriodsBack

    VAR CurrentMarket = SELECTEDVALUE('data'[Market])
    VAR CurrentCategory = SELECTEDVALUE('data'[Category])
    VAR CurrentBrand = SELECTEDVALUE('data'[Brand])

    RETURN
        IF(
            ISBLANK(PrevRank),
            BLANK(),
            CALCULATE(
                [Switch KPIs],
                FILTER(
                    ALL('data'),
                    SWITCH(
                        PeriodType,
                        "Week", 'data'[Wave] = PrevRank,
                        "Month", 'data'[Month Rank] = PrevRank,  
                        "Quarter", 'data'[Quarter Rank] = PrevRank,  
                        "Year", 'data'[Year Rank] = PrevRank  
                    ) &&
                    'data'[Market] = CurrentMarket &&
                    'data'[Category] = CurrentCategory &&
                    'data'[Brand] = CurrentBrand &&
                    NOT(ISFILTERED('data'[Age])) &&
                    NOT(ISFILTERED('data'[Children]))
                )
            )
        )

     
    Probably there were too many unknowns and I didn't explain things the right way, but I really appreciate your time.

    Thanks a lot!
    HO

HOrakova's avatar
HOrakova
Helper I
1 year ago

Hi v-kpoloju-msft  and techies ,

 

I managed to get the measure to work the way I needed it to:

Previous Period Value =
VAR PeriodType = MAX('Period'[Period Type])  
VAR PeriodsBack = SELECTEDVALUE('No of Periods'[Value])
VAR PrevRank = [Switch MAX Period Rank] - PeriodsBack

VAR CurrentMarket = SELECTEDVALUE('data'[Market])
VAR CurrentCategory = SELECTEDVALUE('data'[Category])
VAR CurrentBrand = SELECTEDVALUE('data'[Brand])

RETURN
    IF(
        ISBLANK(PrevRank),
        BLANK(),
        CALCULATE(
            [Switch KPIs],
            FILTER(
                ALL('data'),
                SWITCH(
                    PeriodType,
                    "Week", 'data'[Wave] = PrevRank,
                    "Month", 'data'[Month Rank] = PrevRank,  
                    "Quarter", 'data'[Quarter Rank] = PrevRank,  
                    "Year", 'data'[Year Rank] = PrevRank  
                ) &&
                'data'[Market] = CurrentMarket &&
                'data'[Category] = CurrentCategory &&
                'data'[Brand] = CurrentBrand &&
                NOT(ISFILTERED('data'[Age])) &&
                NOT(ISFILTERED('data'[Children]))
            )
        )
    )

 
Probably there were too many unknowns and I didn't explain things the right way, but I really appreciate your time.

Thanks a lot!
HO