Forum Discussion

HOrakova's avatar
HOrakova
Icon for Helper I rankHelper 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
Icon for Helper I rankHelper I
1 year ago

Hi v-kpoloju-msft ,

 

SELECTEDVALUE is not working with [Period Type] and I use MAX. But anyway when I filter the periods the first selected previous period remain blank.

Best Regards,

HO

v-kpoloju-msft's avatar
v-kpoloju-msft
Icon for Community Support rankCommunity Support
1 year ago

Hi HOrakova,

Try this:

Previous Period =
VAR SelectedPeriodType = MAX('Period'[Period Type])
VAR CurrentPeriodRank = [Switch MAX Period Rank]
VAR PreviousPeriodRank = CurrentPeriodRank - 1

VAR PreviousPeriod =
    SWITCH(
        TRUE(),
        SelectedPeriodType = "Year",
            CALCULATE(
                MAX('Data'[Year Rank]),
                FILTER(ALL('Data'), [Switch MAX Period Rank] = PreviousPeriodRank)
            ),
        SelectedPeriodType = "Quarter",
            CALCULATE(
                MAX('Data'[Quarter Rank]),
                FILTER(ALL('Data'), [Switch MAX Period Rank] = PreviousPeriodRank)
            ),
        SelectedPeriodType = "Month",
            CALCULATE(
                MAX('Data'[Month Rank]),
                FILTER(ALL('Data'), [Switch MAX Period Rank] = PreviousPeriodRank)
            ),
        SelectedPeriodType = "Week",
            CALCULATE(
                MAX('Data'[Wave]),
                FILTER(ALL('Data'), [Switch MAX Period Rank] = PreviousPeriodRank)
            )
    )

RETURN
IF(
    NOT ISBLANK(PreviousPeriod),
    CALCULATE(
        [Switch KPIs],
        FILTER(ALL('Data'), [Switch MAX Period Rank] = PreviousPeriod)
    ),
    BLANK()
)

 
I hope this could resolve your issue, if you need any further assistance, feel free to reach out.
If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you for using Microsoft Community Forum.

  • HOrakova's avatar
    HOrakova
    Icon for Helper I rankHelper I
    1 year ago

    Hi v-kpoloju-msft ,

    There is an error: Query exceeded the available resources.