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

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

Hi HOrakova,

 

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

 

Thank you.

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

Hi HOrakova,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

 

  • HOrakova's avatar
    HOrakova
    Helper I
    1 year ago

    Hi v-kpoloju-msft ,

    Sorry for not answering, but I was off. 
    I use the following measure to find the previous period:

    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(
    ISBLANK(PreviousPeriod),
    BLANK(),
    CALCULATE(
    [Switch KPIs],
    FILTER(ALLSELECTED('Data'), [Switch MAX Period Rank] = PreviousPeriod)
    )
    )

    and it is working correctly when all the periods are selected, but when I select only one - the previous period is empty.

    Period Type is a column from another calculated table:

    Period = {
        ("Year", NAMEOF('Data'[Year]), 3),
        ("Quarter", NAMEOF('Data'[Quarter]), 2),
        ("Month", NAMEOF('Data'[Month]), 1),
        ("Week", NAMEOF('Data'[Week]), 0)
    }
    I use it as a slicer first as Week/Month/Quarter/Year and duplicate as values of selected field.
    • v-kpoloju-msft's avatar
      v-kpoloju-msft
      Community Support
      1 year ago

      Hello HOrakova,

       

      Thank you for sharing your formula. It appears the issue arises when only one period is selected in the slicer, causing the previous period to return blank.

       

      To address this, you can refine your DAX formula to better handle single period selection. Below, I have adjusted the formula to ensure it functions correctly even with a single period selection in the slicer.

      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(
    ISBLANK(PreviousPeriod) && HASONEFILTER('Period'[Period Type]),
    BLANK(),
    CALCULATE(
        [Switch KPIs],
        FILTER(ALLSELECTED('Data'), [Switch MAX Period Rank] = PreviousPeriod)
    )
)

       

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

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

        it gives me an error fetching data.