cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
paulmj21
Regular Visitor

Monthly Running Total

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

Picture1.png

 

 

 

3 ACCEPTED SOLUTIONS
amitchandak
Super User IV
Super User IV

@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])))



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

v-jingzhang
Community Support
Community Support

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. 

062402.jpg

 

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.

View solution in original post

Hi Jing,

This works perfectly! 

Thank you for the sample as well. Much appreciated.

View solution in original post

5 REPLIES 5
v-jingzhang
Community Support
Community Support

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. 

062402.jpg

 

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.

View solution in original post

Hi Jing,

This works perfectly! 

Thank you for the sample as well. Much appreciated.

View solution in original post

amitchandak
Super User IV
Super User IV

@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])))



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

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.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors