Forum Discussion
Power BI DAX
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?
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)
3 Replies
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)
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
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.
