Graph Average Dax Formula by Year and last year YTD and actual year/month filtering category

[miriamLara]

Hi Everyone,

I'm trying to create the graph below with the AVERAGE SUM Incidents by Year and YTD for the actual year and actual+month. The average should consider the 12 months, not just the month with data. Filtering columns and by 'Incidents Tracker'[Category_Operations]

FILTER('Incidents Tracker','Incidents Tracker'[Show at Weekly Report] = "Yes" && ('Incidents Tracker'[Divisions for Report]= "Coke" || 'Incidents Tracker'[Divisions for Report]="Operations" )

I created this measure, but The avg is considering only the monthly data, and I could not put the YTD. I'm using the Calendar table.

Graph that I want to get:

Using the measure:

AVG By Month =

Var __Table = SUMMARIZE(FILTER('Incidents Tracker','Incidents Tracker'[Show at Weekly Report] = "Yes" && ('Incidents Tracker'[Divisions for Report]= "Coke" || 'Incidents Tracker'[Divisions for Report]="Operations" ) ),'Incidents Tracker'[Category_Operations],'Calendar'[YearMonth], "TotalIncidents",SUM('Incidents Tracker'[Total ENV Incidents Count]))

RETURN

AVERAGEX(__Table, [TotalIncidents])

I was able to create this graph:

I'm using the X the Calendar Column

"ReportWeekX",IF( Year([Date])<> YEAR(now()),CONVERT(YEAR([Date]),STRING),CONVERT(FORMAT([Date],"YYYY-MMM"),STRING ) )

I must include the YTD 2023 and change the measure to consider 12 months of data each year.

Can someone help me?

[miriamLara]

Date	Category	Total Count
4/1/2022	Y1 Category	0
4/2/2022	Y1 Category	1
4/3/2022	Y1 Category	0
4/5/2022	Y1 Category	1
4/6/2022	Y1 Category	0
4/7/2022	Y1 Category	1
4/10/2022	Y1 Category	1
4/11/2022	Y1 Category	0
4/12/2022	Y1 Category	1
4/13/2022	Y1 Category	0
4/14/2022	Y1 Category	1
4/15/2022	Y1 Category	0
4/16/2022	Y1 Category	0
4/17/2022	Y1 Category	0
4/20/2022	Y1 Category	0
4/21/2022	Y1 Category	0
4/22/2022	Y1 Category	1
4/25/2022	Y1 Category	0
4/27/2022	Y1 Category	0
4/29/2022	Y1 Category	1
4/30/2022	Y1 Category	0
1/3/2023	Y1 Category	1
1/4/2023	Y1 Category	2
1/6/2023	Y1 Category	0
1/10/2023	Y1 Category	1
1/12/2023	Y1 Category	0
1/13/2023	Y1 Category	0
1/16/2023	Y1 Category	1
1/17/2023	Y1 Category	0
1/19/2023	Y1 Category	0
1/23/2023	Y1 Category	0
1/24/2023	Y1 Category	0
1/25/2023	Y1 Category	0
1/26/2023	Y1 Category	1
1/27/2023	Y1 Category	0
1/28/2023	Y1 Category	0
1/29/2023	Y1 Category	2
1/30/2023	Y1 Category	1
1/31/2023	Y1 Category	1
2/1/2023	Y1 Category	0
2/2/2023	Y1 Category	0

[lbendlin]

This is your sample data

It is not sufficient to produce your expected outcome.

[miriamLara]

Hi @lbendlin , I tried to copy my data here, but It is more than 200,000 characters. How can I upload a file here? I can share a file in one drive but I need your email. Thanks for your help.

[lbendlin]

https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

[miriamLara]

Can you try to download the file here?

https://onecliffs-my.sharepoint.com/:x:/g/personal/miriam_lara_clevelandcliffs_com/EVqOTQZ-n7BKq9Af8... 

[lbendlin]

Yes.  Something like this?

[miriamLara]

I need to show in BARs the avg year divided by 12 by year, the YTD 2023 (divided by months in the year), and the Actual year avg by months in the same graph. The line is only a Target of 11.

I'm with difficulty creating the X-axis to show like the graph below in powerBi.

Like the graph below:

[lbendlin]

If you want a custom X axis you need to create a reference table with the desired values and their sort order.

It is a fallacy to show the same data twice on the same visual   (2023 YTD and the months of 2023).  I can't help you with that.

[miriamLara]

Thanks @lbendlin for your help and for trying to help me. I will try to create the tables references and create my own X-axis attribute to show in the graph.

[miriamLara]

@lbendlin, thanks for your response

Follow the records with the monthly data samples to see if you can help me to create the Dax Average function.

[lbendlin]

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...