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 ,

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.

  • 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