locked
cumulative total of measure RRS feed

  • Question

  • How to calculate the cumulative totals of measure in following example:

     The model contains a date table and fact table called Internal.The measure EOMClosed is obtained by using

    EOMClosed = CALCULATE(distinctcount(Internal[ID]),userelationship(Internal[CloseDate],'Date'[Date]))

    The measure when sliced by Date dim gives me number of tickets that got closed each month.I want to make it like cumulative/running.

     EOMOpen is number of open tickets each month sliced by date like :

    EOMOpen = CALCULATE(DISTINCTCOUNT([ID]),FILTER(ALL(Internal),(Internal[CloseDate]=blank()||Internal[CloseDate]>EOMONTH([OpenDate],0))&&Internal[FirstDate]<=max('Date'[Date])))

    I tried to do the same with EOMClosed but turns out the userelationship is not propogating the context to filter function :

    EOMClosed = CALCULATE(distinctcount(Internal[ID]),userelationship(Internal[CloseDate],'Date'[Date]),filter(Internal,[CloseDate]<=max(Date[Date]))

    Pls guide me to fix the EOMClosed to get cumulative values.

    Here is a sample file:

     





    Thursday, August 9, 2018 8:21 PM

Answers

  • Hi msdnpublic1234,

    Thanks for your response.

    Based on your sample data and expected output, please try below DAX formula:

    EOMCunulativeClosed =
                CALCULATE( DISTINCTCOUNT(Internal[ID]),
                          FILTER( ALL(Internal),
                                  Internal[CloseDate]<Max ('Date'[Date] ) && 
                                  Internal[CloseDate] <> BLANK()
                                  )
                           )


    Best Regards
    Willson Yuan
    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

    • Proposed as answer by alexander fun Friday, August 10, 2018 9:04 AM
    • Marked as answer by msdnpublic1234 Friday, August 10, 2018 1:01 PM
    Friday, August 10, 2018 6:27 AM