Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Super User

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

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.

Hi Jing,

This works perfectly! 

Thank you for the sample as well. Much appreciated.

amitchandak
Super User
Super User

@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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.