Forum Discussion

paulmj21's avatar
paulmj21
Regular Visitor
5 years ago
Solved

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

 

 

 

Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

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

  • v-jingzhang's avatar
    v-jingzhang
    5 years ago

    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.

  • paulmj21's avatar
    paulmj21
    5 years ago

    Hi Jing,

    This works perfectly! 

    Thank you for the sample as well. Much appreciated.