Forum Discussion

Fusilier2's avatar
Fusilier2
Icon for Helper V rankHelper V
1 year ago
Solved

KPI visual to show previous month

I have monthly data in my data table.

In the PBI report I have a number of KPI visuals. They automatically always show the latest months data without any filtering.

Is there any way of getting a KPI visual to automatically show the previous month's values (T-1)? I also want to show change between current month & previous month. Can that be done without filtering?

I'm trying to replicate something similar to this:

 

Need Help
  • Fusilier2's avatar
    Fusilier2
    1 year ago

    Looking good but the previous period value dax is not returning anything. Have I missed something?

     

    Prior Month =
    CALCULATE(
        SUM(HR_data[No. of Apprentice]),
        FILTER(
            HR_data,
            HR_data[Period]=EOMONTH(MAX(HR_data[Period]),-1)
        )
    )

4 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Icon for Super User rankSuper User
    1 year ago

    Fusilier2 Yes, you can achieve this in Power BI by creating measures that calculate the values for the current month and the previous month, and then use these measures in your KPI visuals. 

    Create a measure for the current month's value:

    DAX
    CurrentMonthValue =
    CALCULATE(
    SUM('YourTable'[YourValueColumn]),
    FILTER(
    'YourTable',
    'YourTable'[DateColumn] = MAX('YourTable'[DateColumn])
    )
    )

     

    Create a measure for the previous month's value:

    DAX
    PreviousMonthValue =
    CALCULATE(
    SUM('YourTable'[YourValueColumn]),
    FILTER(
    'YourTable',
    'YourTable'[DateColumn] = EOMONTH(MAX('YourTable'[DateColumn]), -1)
    )
    )

     

    Create a measure for the change between the current month and the previous month:

    DAX
    MonthOverMonthChange =
    [CurrentMonthValue] - [PreviousMonthValue]

     

    Create a measure for the percentage change:

    DAX
    MonthOverMonthChangePercent =
    DIVIDE([MonthOverMonthChange], [PreviousMonthValue], 0)

     

    Set the CurrentMonthValue as the main value.
    Set the MonthOverMonthChange or MonthOverMonthChangePercent as the target or secondary value.

    • Fusilier2's avatar
      Fusilier2
      Icon for Helper V rankHelper V
      1 year ago

      Looking good but the previous period value dax is not returning anything. Have I missed something?

       

      Prior Month =
      CALCULATE(
          SUM(HR_data[No. of Apprentice]),
          FILTER(
              HR_data,
              HR_data[Period]=EOMONTH(MAX(HR_data[Period]),-1)
          )
      )
  • danextian's avatar
    danextian
    Icon for Super User rankSuper User
    1 year ago

    Hi Fusilier2 

     

    Actual formula may vary depending on your model but would be more or less like these

     

    First calculate the max date

    Max Date =
//this calculates the max date in 'table' regardless of the filters applied to it
CALCULATE ( MAX( 'table'[date] ), REMOVEFILTERS ( 'table' ) )

     

    Current Month

    Current Month Value = 
CALCULATE (
    -- Sum values where the date is in the same month as [Max Date]
    SUM ( 'table'[value] ),
    FILTER (
        ALL ( 'table'[date] ),
        EOMONTH ( 'table'[date], 0 ) = EOMONTH ( [Max Date], 0 )
    )
)

     

    Previous Month

    Previous Month Value = 
CALCULATE (
    -- Sum values where the date is in the prior month as [Max Date]
    SUM ( 'table'[value] ),
    FILTER (
        ALL ( 'table'[date] ),
        EOMONTH ( 'table'[date], 0 ) = EOMONTH ( [Max Date], -1 )
    )
)

     

    Change =
[Current Month Value] - [Previous Month Value]

     

  • v-csrikanth's avatar
    v-csrikanth
    Icon for Community Support rankCommunity Support
    1 year ago

    Hi Fusilier2 

    Thank you for being part of the Microsoft Fabric Community.

    As highlighted by danextian bhanu_gautam , the proposed approach appears to effectively address your requirements. Could you please confirm if your issue has been resolved?
    If you are still facing any challenges, kindly provide further details, and we will be happy to assist you.


    If the above information is helpful, please give us Kudos and mark the response as Accepted as solution.
    Best Regards,
    Community Support Team _ C Srikanth.