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

Need Help
Show and Tell
Tips and Tricks
Tutorial Requests

3 Replies

  • Uzi2019's avatar
    Uzi2019
    Icon for Community Champion rankCommunity Champion
    1 year ago

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