topic Re: Graph Average Dax Formula by Year and last year YTD and actual year/month filtering category in DAX Commands and Tips

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

miriamLara — Tue, 11 Jul 2023 17:40:48 GMT

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?

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

lbendlin — Sun, 16 Jul 2023 14:07:09 GMT

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-Forum/ba-p/963216
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/1447523

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

miriamLara — Mon, 17 Jul 2023 16:21:36 GMT

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.

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

miriamLara — Mon, 17 Jul 2023 16:22:24 GMT

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

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

lbendlin — Wed, 19 Jul 2023 00:16:38 GMT

This is your sample data

It is not sufficient to produce your expected outcome.

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

miriamLara — Wed, 19 Jul 2023 13:31:58 GMT

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.

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

lbendlin — Wed, 19 Jul 2023 13:35:50 GMT

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

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

miriamLara — Wed, 19 Jul 2023 13:39:29 GMT

Can you try to download the file here?

https://onecliffs-my.sharepoint.com/:x:/g/personal/miriam_lara_clevelandcliffs_com/EVqOTQZ-n7BKq9Af86dGkscBpZXBE8TqICGZIpQUqc5T5g?e=zc6bc9

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

lbendlin — Wed, 19 Jul 2023 14:12:42 GMT

Yes. Something like this?

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

miriamLara — Wed, 19 Jul 2023 14:41:33 GMT

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:

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

lbendlin — Wed, 19 Jul 2023 21:05:40 GMT

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.

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

miriamLara — Wed, 19 Jul 2023 22:01:44 GMT

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.