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

 I don't know if this help.

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

Hi HOrakova,
Thank you for reaching out to the Microsoft fabric community forum. Thank you techies, for your inputs on this issue.


After thoroughly reviewing the details you provided, I was able to reproduce the scenario, and it worked on my end. I have used it as sample data on my end and successfully implemented it.


Preious Period column dax measure:

Previous Period =
VAR CurrentPeriod = MAX(data[Switch Period])  -- Identify the current period selected
RETURN
    CALCULATE(
        SUM(data[Value]),
        FILTER(
            ALL(data),  -- Ignores all filters applied except date
            data[Switch Period] = CurrentPeriod - 1  -- Finds the previous period
        )
    )


I am also including .pbix file for your better understanding, please have a look into it:


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 ,

     

    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.