Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I have a database relating to corrective action(NCR) requests raised. The columns include:
Date Raised
Date Closed
Priority
Age, etc.
Basically, I want to show by month how many have been raised, how many have been closed and what the cumulative total that were still open for that month.
I have attached a screen shot of the visuals I have, which Im happy with, except cant seem to get the black and green visual, which shows date raised and date closed by month to also show a line chart of cumulative open by month.
Any help will be much appreciated.
Thanks Paul
Solved! Go to Solution.
@paulmj21 , for monthly cumulative you can use dates mtd. You have to join both dates with the date table. and one of the joins will inactive, say Date Closed is inactive, that you can activate using userelationship
MTD Sales = CALCULATE(SUM(Table[Age]),DATESMTD('Date'[Date]))
calculate( calculate( SUM(Table[age]),USERELATIONSHIP ('Table'[Date closed], 'Date'[Date])),DATESMTD('Date'[Date]))
overall cumulative example
Cumm Sales = CALCULATE(SUM(Table[Age]),filter(allselected(date),date[date] <=max(date[Date])))
Hi @paulmj21
You need to have a Date table in the model. Create relationships between 'Date'[Date] and 'Table'[Date Raised] & 'Table'[Date Closed]. Because two relationships exist between two tables, one relationship is active and the other one should be inactive.
Then use the Date column from Date table into the chart as Shared Axis field. Create two measures to count the Raised and Closed by month. And create a measure which will use above two measures to calculate the cumulative difference. Below are the measures. In my model, the relationship between 'Table'[Date Closed] and 'Date'[Date] is inactive, so I use USERELATIONSHIP function to activate it in the measure.
Date Raised Count = COUNT('Table'[Date Raised])
Date Closed Count = CALCULATE(COUNT('Table'[Date Closed]),USERELATIONSHIP('Table'[Date Closed],'Date'[Date]))
Cumulative Open Count = CALCULATE([Date Raised Count]-[Date Closed Count],FILTER(ALL('Date'),'Date'[Date]<=MAX('Date'[Date])))
I attached a sample file for your reference. Let me know if you have any questions.
Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.
Hi Jing,
This works perfectly!
Thank you for the sample as well. Much appreciated.
Hi @paulmj21
You need to have a Date table in the model. Create relationships between 'Date'[Date] and 'Table'[Date Raised] & 'Table'[Date Closed]. Because two relationships exist between two tables, one relationship is active and the other one should be inactive.
Then use the Date column from Date table into the chart as Shared Axis field. Create two measures to count the Raised and Closed by month. And create a measure which will use above two measures to calculate the cumulative difference. Below are the measures. In my model, the relationship between 'Table'[Date Closed] and 'Date'[Date] is inactive, so I use USERELATIONSHIP function to activate it in the measure.
Date Raised Count = COUNT('Table'[Date Raised])
Date Closed Count = CALCULATE(COUNT('Table'[Date Closed]),USERELATIONSHIP('Table'[Date Closed],'Date'[Date]))
Cumulative Open Count = CALCULATE([Date Raised Count]-[Date Closed Count],FILTER(ALL('Date'),'Date'[Date]<=MAX('Date'[Date])))
I attached a sample file for your reference. Let me know if you have any questions.
Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.
Hi Jing,
This works perfectly!
Thank you for the sample as well. Much appreciated.
@paulmj21 , for monthly cumulative you can use dates mtd. You have to join both dates with the date table. and one of the joins will inactive, say Date Closed is inactive, that you can activate using userelationship
MTD Sales = CALCULATE(SUM(Table[Age]),DATESMTD('Date'[Date]))
calculate( calculate( SUM(Table[age]),USERELATIONSHIP ('Table'[Date closed], 'Date'[Date])),DATESMTD('Date'[Date]))
overall cumulative example
Cumm Sales = CALCULATE(SUM(Table[Age]),filter(allselected(date),date[date] <=max(date[Date])))
Thank you and I appreciate your effort to answer, but I find this answer confusing. (I anm very new to Power BI and self taught). Am I meant to calculate age as well? I have no sales information, so context makes it difficult to understand your answer.
I only have date closed, date raised that Im interested in. The column chart I have shows, by month, how many NCRs raised for a particular month and how many closed for the same month. All I want to do is calculate the difference.
E.g. In January I raised 7 NCRs and Closed 4. Therefore 3 are still open, in February I raised 5 and closed 4, so 1 still open for February, but the **bleep** total still open is 4. Hope that was a better explanation.
Cheers,
Paul
Sorry, dont know why in my reply there is a **bleep**, its meant to read cumulative.
User | Count |
---|---|
128 | |
109 | |
100 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |