locked
Running Total in DAX Problem RRS feed

  • Question

  • Hello;

    I have a running total for all Opened Problem Reports that works just fine:

    Open Cnt:=CALCULATE(COUNTA('Problem Reports'[PR ID]), FILTER(ALL(SubmittedDate), SubmittedDate[Date] <= MAX(SubmittedDate[Date])))

    Then I have another one for closed Problem Reports that looks like this:

    Closed Cnt:=CALCULATE(SUM([ClosedStatus]), FILTER(ALLSELECTED(ClosedDate), ClosedDate[Date] <= MAX(ClosedDate[Date])))

    If I chart this, I get a nice curve for the Opened Problem Reports but the Closed Problem Reports doesn't give me a running total, I get line bits depending on the month and it represents the closed total for that month.

    If I change the Closed Cnt to use the Submitted Date like so:

    Closed Cnt:=CALCULATE(SUM([ClosedStatus]), FILTER(ALLSELECTED(SubmittedDate), SubmittedDate[Date] <= MAX(SubmittedDate[Date])))

    I get an proper running total, the problem is that my running total is based on the submitted date and not the Closed Date.

    How can I get the running total for Closed Cnt to work on the Closed date and chart properly.

    Whether I use either SubmittedDate or Closed date, my total count looks the same, it's only when I chart it and break it down into Months (or whatever period the user choses) that things go south.

    Any help would be greatly appreciated.

    Thanks in advance.


    Thanks Ron...

    Thursday, December 8, 2016 4:33 PM

Answers

All replies

  • Hi RonInOttawa,

    When you Closed Problem Reports, where the date in Row level is from? Please verify and check, you should select ClosedDate[Date] as Row levels. Based on your description, it may be caused by adding SubmittedDate[Date] as Row level.

    When you select the SubmittedDate[Date], the row context is from SubmittedDate[Date] rather than ClosedDate[Date]. So it doesn't calculate a running total, if the result total in your Closed Problem Reports is same?

    Best Regards,
    Angelia

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, December 9, 2016 8:21 AM
  • Hi Angelia;

    Thanks for your response, if you look at my second code sample and explanation, if I use the closed date, I don't get the running total, instead I get a total for the given period only. It's only if I use the closed count with the Submitted date do I get a true running total and this is wrong, because PRs aren't necessarily closed the same week/month that they are opened on.

    Cheers.


    Thanks Ron...

    Monday, December 12, 2016 1:58 PM
  • Hi Ron,

    You might also find the construction of a calculated table easier for these cases like described here: http://www.thebiccountant.com/2016/12/01/analyzing-events-with-a-duration-in-dax-further-simplification/


    Imke Feldmann TheBIccountant.com

    • Marked as answer by RonInOttawa Tuesday, December 13, 2016 2:22 PM
    Tuesday, December 13, 2016 8:55 AM
    Answerer