Month on Month calculation for the whole year - Line Graph

[Kostas]

Hello, 

As before need you help once more. 
I want to create a line graph or a 100% bar chart with an additional line axis. 
At the X axis I want to have the date column from my calendar table. 

My main table contains the following columns:

1) Unique ID number
2) Rating (Effective, Not Effective, Not Rated)

3) Rating Issue Date (contains an inactive relationship with the calendar table).

The line graph need to show for every month, the average ID's with a rating of 'Not Effective' for the last 12 months up to the month that the X axis. 

For example, for the X Axis which will be showing March 2023, the line will show the percentage of ID's that were at the last 12 months as 'Not Effective' in comparison to the total ID's for the whole 12 months based on the Rating Issue Date. That means:

Rating Issue Date Period: 01/04/2022 - 31/03/2023:

Total ID's: 190

Total ID's with rating as 'Not Effective': 80

When X axis is March 2023, the line should be at 42% (80/190)

I used to have a measure that worked here but for some reason the last month is not working anymore but showing the average of the specific month only rather than considering the full 12 months (i.e. If in March 2023 had 5 ID's in total and 4 with 'Not Effective' rating then it will show 80% without considering the full year data).

Any help or hints will be greatly appreciated

[v-yangliu-msft · Accepted solution]

Hi  @Kostas ,

I created some data:

Here are the steps you can follow：

1. Create measure.

Measure =
var _startdate=
EOMONTH(MAX('Table 2'[Date]),-12)
var _start=
DATE(YEAR(_startdate),MONTH(_startdate),1)
var _end=
EOMONTH(MAX('Table 2'[Date]),0)
var _countall=
COUNTX(
    FILTER(ALL('Table'),'Table'[Rating Issue Date]>=_start&&'Table'[Rating Issue Date]<=_end),[Unique ID number])
var _countNotRated=
COUNTX(
    FILTER(ALL('Table'),'Table'[Rating Issue Date]>=_start&&'Table'[Rating Issue Date]<=_end&&'Table'[Rating]="Not Rated"),[Unique ID number])
return
DIVIDE(_countNotRated,_countall)

2. Result:

Best Regards,

Liu Yang

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

[Kostas]

Hello @v-yangliu-msft , 

The code worked perfectly, something like that I had in mind but couldn't make it work thank you!

Can I ask a follow-up question please? 

What if I need to apply an additional filter on an additional table let's call it Table_2. 

Table_2 contains two columns: {'ID','Risk'} --> Multiple risks can be assigned to a single ID so have a secondary table to provide that data. 
Relationship: 'Table_2'[ID] *-1 'Table 1'[ID]

Filter to apply: 'Table_2'[Risk] = "Operational"

So on the second COUNTX that you create to apply after the rating filter the filter for 'Table_2'[Risk] = "Operational".

It seems that COUNTX cannot consider filters from other tables no matter the relationship; could you please assist?

Thanks in advance for your time.