Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Kostas
Helper IV
Helper IV

Month on Month calculation for the whole year - Line Graph

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

1 ACCEPTED SOLUTION
v-yangliu-msft
Community Support
Community Support

Hi  @Kostas ,

I created some data:

vyangliumsft_0-1713257344902.png

 

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:

vyangliumsft_1-1713257344914.png

 

 

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

View solution in original post

2 REPLIES 2
v-yangliu-msft
Community Support
Community Support

Hi  @Kostas ,

I created some data:

vyangliumsft_0-1713257344902.png

 

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:

vyangliumsft_1-1713257344914.png

 

 

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

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.

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.