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, thank you for the fast reply!
When I add 

&& HASONEFILTER('Period'[Period Type])

it gives me an error fetching data.

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

Hi ashwinkolte,

Thank you for the update. The issue might be due to HASONEFILTER causing conflicts if 'Period'[Period Type] isn’t directly filtered or used in the slicer. I recommend replacing that part with:

 


ISBLANK(SELECTEDVALUE('Period'[Period Type]))

 

This approach will be more stable and should prevent the "fetching data" error. Please let me know if this solution works better.

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.

  • v-kpoloju-msft's avatar
    v-kpoloju-msft
    Community 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
    Helper I
    1 year ago

    Hi v-kpoloju-msft ,

    There is an error: Query exceeded the available resources.

  • HOrakova's avatar
    HOrakova
    Helper 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