Power BI DAX

[cheeto92]

Hi Everyone,

I have a chart that shows the work order volume by month. There are two columns being used in the chart -> the date column and the Request ID column they both come from the Work Order table.

I try to do a cumulative average across the months and i'm getting the wrong average. Its basically calculating the actual request id number and not the count. ANy help please?

 

[ToddChitt · Accepted solution]

OK. I think we can get close. 

Unfortunately there is a limitation that you cannot use a Measure as a Constant Line on a chart. But we can probably get around that.

Try this:

Create a measure that is [Monthly Count] = COUNTROWS('Work Order table')

Create a measure that is [Count of Months] = COUNTDISTINCT ( MONTH ( 'Work Order table'[Date]) )

Create a measure that is [Monthly Average] = DIVIDE ( [Monthly Count], [Count of Months], 0 )

That will get you close, but know that January 2024 (month = 1) will be treated the same as January 2023 (also month = 1). You may need an intermediate column: [YYYYMM] = YEAR ( [some date] ) * 100 + MONTH ( [some date] ). Do your [Count of Months] on that column)

[ToddChitt]

What number are you expecting to see for each month of the chart? Do you want a 'constant (flat) line' at:

( 312  + 349 + 327 + 148 ) / 4 ? = 284

[cheeto92]

Hi Todd,

i am expecting to see that 284. I want the Dax to be dynamic to take in more months as i keep going into the year as well.