Solved: Diff between selected and previous period

HOrakova · ‎03-30-2025

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!

HOrakova · ‎04-16-2025

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

View solution in original post

HOrakova · ‎04-16-2025

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

techies · ‎03-30-2025

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

)

― Power BI | Microsoft Fabric | PL-300 | DP-600 | Blog: medium.com/@cseprs_54978

HOrakova · ‎03-30-2025

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

techies · ‎03-30-2025

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

)

― Power BI | Microsoft Fabric | PL-300 | DP-600 | Blog: medium.com/@cseprs_54978

HOrakova · ‎03-30-2025

Unfortunately it is not working.
I uploaded a sanitized copy of my pbix here:
https://we.tl/t-pbH4A7Azfv
Hope this helps to understand my case.

Best Regards!

techies · ‎03-31-2025

Hi, i would suggest using a date table?

― Power BI | Microsoft Fabric | PL-300 | DP-600 | Blog: medium.com/@cseprs_54978

HOrakova · ‎03-31-2025

Hi @techies ,
Then how can I add the both slicers for Period Type and the relevant dates?

HOrakova · ‎03-30-2025

I don't know if this help.

v-kpoloju-msft · ‎04-02-2025

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 · ‎04-02-2025

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 · ‎04-03-2025

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 · ‎04-05-2025

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 · ‎04-09-2025

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 · ‎04-13-2025

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 · ‎04-13-2025

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 · ‎04-13-2025

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 · ‎04-14-2025

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.

HOrakova · ‎04-14-2025

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

v-kpoloju-msft · ‎04-14-2025

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.