Forum Discussion

cheeto92's avatar
cheeto92
Frequent Visitor
2 years ago
Solved

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 Orde...
Need Help
  • ToddChitt's avatar
    ToddChitt
    2 years ago

    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's avatar
ToddChitt
Super User
2 years ago

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)