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 stepping in. I use a slicer parameter for the type of period (week/month/quarted/year) and another one for the respective period. In your formula I was able to switch the last row with:

 

 SELECTEDVALUE(Period[Period Type]) = CurrentPeriod - 1

but an error fetching data occurs.
v-kpoloju-msft's avatar
v-kpoloju-msft
Community Support
1 year ago

Hi HOrakova,
Apologies for the late reply. After reviewing the details you provided, I have identified few workarounds that may help resolve the issue. Please follow these steps:

If you haven't already, create a Calendar Table that includes all dates and links it to your fact table (data). This will ensure that Power BI can handle date-based calculations correctly.

Instead of directly subtracting -1 from CurrentPeriod, try using SWITCH to handle different period types dynamically.

 

Previous Period =
VAR CurrentPeriod = MAX(data[Switch Period])
VAR PeriodType = SELECTEDVALUE(Period[Period Type])  -- Get the selected period type
RETURN
    SWITCH(
        TRUE(),
        PeriodType = "Week", CALCULATE( SUM(data[Value]), FILTER( ALL(data), data[Switch Period] = CurrentPeriod - 1 ) ),
        PeriodType = "Month", CALCULATE( SUM(data[Value]), FILTER( ALL(data), data[Switch Period] = CurrentPeriod - 1 ) ),
        PeriodType = "Quarter", CALCULATE( SUM(data[Value]), FILTER( ALL(data), data[Switch Period] = CurrentPeriod - 1 ) ),
        PeriodType = "Year", CALCULATE( SUM(data[Value]), FILTER( ALL(data), data[Switch Period] = CurrentPeriod - 1 ) ),
        BLANK()  -- Return blank if no match
    )

 

This approach ensures that the previous period is correctly identified based on the selected period type.

If the error persists, use this measure to check if Period Type and CurrentPeriod are returning expected values:

 

DebugPeriod =
VAR CurrentPeriod = MAX(data[Switch Period])
VAR SelectedPeriodType = SELECTEDVALUE(Period[Period Type])
RETURN
"Current Period: " & FORMAT(CurrentPeriod, "YYYY-MM-DD") & 
" | Selected Period Type: " & SelectedPeriodType

 

Ensure the Period Table (used for the slicer) is a disconnected table and does not filter the fact table directly. Instead, apply logic in the measure to derive the correct period.

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,

     

    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.