Dynamic Percentage change in KPI

[Akshaymanjunath]

I need to create a growth rate visual in KPI

When a particular time period is selected in the global filter date, it should display data comparing the previous period

This should work dynamically as explained in short below

For example:

If June 2024 and july 2024 is selected in the date filter the growth rate should display in comparison to april 2024 and may 2024

If august 2024 is selected in the date filter the growth rate should display in comparison to july 2024.

if july 2024, aug 2024 and sept 2024 is selected in the date filter then, the growth rate should display in comparison to april 2024, may 2024, june 2024.

[v-xuxinyi-msft]

Hi @Akshaymanjunath 

Thanks for the reply from Uzi2019, please allow me to provide another insight:

My sample:

Create a calculated table as the slicer

Slicer = VALUES('Table'[Date])

Create a measure as follows

MEASURE = 
VAR _countSelectedMonth =
    CALCULATE (
        COUNT ( Slicer[Date].[Month] ),
        FILTER ( 'Slicer', [Date] IN VALUES ( Slicer[Date] ) )
    )
VAR _minSelected =
    CALCULATE (
        MIN ( 'Slicer'[Date] ),
        FILTER ( 'Slicer', [Date] IN VALUES ( Slicer[Date] ) )
    )
VAR _maxSelected =
    CALCULATE (
        MAX ( 'Slicer'[Date] ),
        FILTER ( 'Slicer', [Date] IN VALUES ( Slicer[Date] ) )
    )
VAR _kpi1 =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            'Table',
            [Date] >= EDATE ( _minSelected, - _countSelectedMonth )
                && [Date] <= EOMONTH ( _minSelected, -1 )
        )
    )
VAR _kpi2 =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            'Table',
            [Date] >= EDATE ( _minSelected, 0 )
                && [Date] <= EOMONTH ( _maxSelected, 0 )
        )
    )
RETURN
    DIVIDE ( _kpi2 - _kpi1, _kpi1 )

Output:

Since I don't know how your KPIs are created, hopefully the provided DAX should work fine. If it doesn't work for your scenario, please provide some sample data so that we can help you better. How to provide sample data in the Power BI Forum - Microsoft Fabric Community Or show them as screenshots or pbix. Please remove any sensitive data in advance. If uploading pbix files please do not log into your account.

Best Regards,
Yulia Xu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Akshaymanjunath]

Previous amount is not displaying when a month having 30 day period is selected in the date filter(for example: june, feb, apr, sept, nov, etc) it will not display any results. when july month is selected from 1st to 31st it will show the previous 31 days comparison as expected.

All these are in day format how to setup the date filter in month format, when i tried to use the month hierarchy of the date field currently used the formula is not working.

I need to setup 2 separate filters monthly and yearly next to each other so based on the selection of the combination of the filters it should work, 

Can you please help me with getting monthly and yearly selection in date filter work with the same way as its working for day wise

start of the period = FIRSTDATE('date'[Date])

end of this period = LASTDATE('date'[Date])

days in this period = DATEDIFF([start of the period],[end of this period],DAY)+1

start of the previous period = [end of the previous period] - [days in this period]+1

end of the previous period = [start of the period]-1

amount = sum(amount)

previous amount = 

CALCULATE(

    [amount],

    DATESBETWEEN('date'[Date], [start of the previous period], [end of the previous period])

)

amount growth rate = ([amount]-[Previousamount])/([Previousamount])

[Uzi2019]

Hi @Akshaymanjunath 

You can try below dax for dynamic comparison:

LastPeriod =

VAR selectedDays =

    CALCULATE ( COUNT ( Calendar[Date] ), ALLSELECTED ( Calendar[Date] ) )

VAR minDate =

    CALCULATE ( MIN ( 'Calendar'[Date] ), ALLSELECTED ( 'Calendar'[Date] ) )

RETURN

        CALCULATE (

            SUM (SalesTable[SalesColumn] ),

            DATESINPERIOD ( 'Calendar'[Date], minDate - 1, - selectedDays, DAY )

        )

or try below video for better understanding
https://www.youtube.com/watch?v=Wwo-tY0B9pY

I hope this might help you to solve your probem.