KPI Reporting

[ArchStanton]

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 there is no trend to report on, however, I'm not quite sure what the best practice will be, say after 6 months?
I would like to show small line charts to track movement over 12 months.

This may sound like a silly question (it probably is!) but would I need to build a measure for each month? A card on the first of every month will only ever show a number at a point in time, so I'm not really sure how to track the monthly numbers other than to manually transfer these into an excel file, link that to my PowerBI report and build an monthly 'x' axis based on my excel.

I'd be grateful for any advice or ideas on this.

Thanks

[Anonymous]

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]

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?

[ArchStanton]

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.