Forum Discussion

Akshaymanjunath's avatar
Akshaymanjunath
Frequent Visitor
1 year ago

Dynamic Percentage change in KPI

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 a...
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
Anonymous's avatar
Anonymous
Not applicable
1 year ago

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's avatar
Akshaymanjunath
Frequent Visitor
1 year ago

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])