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 the diff between selected and previous period. (The period is selected by the user.) There are filters by market, age, gender, brand ect. I added one slicer by period type and one with corresponding dates. The following measure is not showing the correct last period and I don't know how to fixed it:

 

Previous Period = CALCULATE( SUM(data[Value]),
                            FILTER( ALLSELECTED(data),
                                    [Switch Period] = MAX(data[Wave]) - 1)
                                    )
)


Also, the previous period must show previous period regardless of the filter selection.
This time I'm attaching a pbix.

Best Regards!

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

21 Replies

  • techies's avatar
    techies
    Super User
    1 year ago

    Hi HOrakova please try this previous period value

     

    Previous Period Value =
    VAR SelectedDate = MAX(data[Date])  
    VAR PreviousDate = CALCULATE(
        MAX(data[Date]),
        ALL(data),
        data[Date] < SelectedDate  
    )

    RETURN
    CALCULATE(
        SUM(data[Value]),
        ALL(data),  
        data[Date] = PreviousDate
    )
    • HOrakova's avatar
      HOrakova
      Helper I
      1 year ago

      Hi techies ,
      How will look your formula when I use parameter instead of [Date]?

      • techies's avatar
        techies
        Super User
        1 year ago
        Previous Period Value =
        VAR SelectedDate = SELECTEDVALUE('Date Parameter'[Date Selection])  
        VAR PreviousDate =
            CALCULATE(
                MAX('data'[Date]),  
                ALL('data'),  
                'data'[Date] < SelectedDate  
            )

        RETURN
        CALCULATE(
            SUM('data'[Value]),  
            ALLSELECTED('data'),  
            'data'[Date] = PreviousDate
        )
  • HOrakova's avatar
    HOrakova
    Helper I
    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

    Actually I'm getting error message "The file type (.pbix) is not supported."

    • 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.