Forum Discussion

ArchStanton's avatar
ArchStanton
Power Participant
2 years ago

KPI Reporting

Hi, I have a report that is connected to live data to which I have recently added 12 new KPIs. These KPI's are being presented in mixture of Cards and Matrix tables.   As this is the first month ...
Need Help
Show and Tell
Tips and Tricks
Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi ArchStanton 

 

In Power BI, KPI trends can be tracked over time without having to manually create metrics for each month.

 

Create metrics to calculate KPIs for the current month, previous months, and any other time period you are interested in.

 

Use these dynamic measures in line graphs.

 

For line charts, you can set the x-axis to the month column in the date table and the y-axis to the dynamic measure. This will give you a 12-month trend.

 

Here I have provided a simple example.

 

Here's some dummy data

 

“Table”

 

Create a measure.

 

Measure = 
var _month = MONTH(SELECTEDVALUE('Table'[Date]))
return
CALCULATE(SUM('Table'[values]), FILTER(ALL('Table'), MONTH('Table'[Date]) =_month))

 

Here is the result.

 

 

Regards,

Nono Chen

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

 

 

  • ArchStanton's avatar
    ArchStanton
    Power Participant
    2 years ago

    Thank you for your reply and help on this, this is very useful.

    My data model is a bit more complicated, let me explain.

     

    I have a measure that calculates how many cases are over 3yrs old below:

     

     

    # Cases 3 Yrs or Older = 
    CALCULATE(
        COUNTROWS(FILTER('Cases',
        'Cases'[Case Length] > 1095)),
        'Cases'[statecode] = "Active")

     

     

    1095 days = 3 years

    The case length is based on the 'Created On' date column in my Cases table which has an active relationship with my Calendar Date2 table = 'Cases' [Created On] and 'Date2' [Date].

    How would you suggest I track this number at the beginning of every month?
    I would like to see the total as at each year, so currently there are 1200, I would like to see what this total was in 2022/23 etc - is that even possible?
    I have tried modifying your example code but I'm not getting the values I want, indeed the x axis stops at Mar 2021 which is 3 years ago, I want it to show the current month Apr, and then next month it will show Apr & May and so on.

     

     

     

    MEASURE =
VAR _month =
    MONTH ( SELECTEDVALUE ( 'Cases'[Created On] ) )
RETURN
    CALCULATE (
        COUNTROWS ( FILTER ( 'Cases', 'Cases'[Case Length] > 1095 ) ),
        'Cases'[statecode] = "Active",
        FILTER ( ALL ( 'Date2' ), MONTH ( 'Date2'[Dates] ) = _month )
    )

     

     

     The code gives me this chart which is incorrect:

     

    Unfortunately I cannot share my data as its sensitive and large, the main fact table has 40,000 rows, multiple different date columns, and over 40 columns. (I inherited this by the way)!

     

    Hope that makes sense? Look forward to your reply.

  • ArchStanton's avatar
    ArchStanton
    Power Participant
    2 years ago

    I would like the measure to count all items that are over 3yrs old now and then show what that total would have been in 2021, 2022 and 2023.

     

    # Cases 3 Yrs or Older = 
    CALCULATE(
        COUNTROWS(FILTER('Cases',
        'Cases'[Case Length] > 1095)),
        'Cases'[statecode] = "Active")

     

    This gives me the current total, 1095 is the number of days in 3yrs. 

     

    I have a Created On date thats linked to my 'Date2'.[Date] calendar table.

    Is this possible do you think?