Forum Discussion

Nicharee2205's avatar
Nicharee2205
Frequent Visitor
2 years ago
Solved

Cumulative SUM

Hi everyone, I'm working on a Power BI report where I need to calculate a cumulative count of unfinished work and plot it by month. Additionally, I need this measure to respect multiple slicers incl...
Need Help
Tutorial Requests
  • BeaBF's avatar
    BeaBF
    2 years ago

    Nicharee2205 Hi!

     

    Try with:

    CumulativeTest2 =
    VAR CurrentDate = MAX('BD Case'[ConvertedDate])
    RETURN
    CALCULATE(
    COUNTROWS('BD Case'),
    FILTER(
    ALLSELECTED('BD Case'),
    'BD Case'[ConvertedDate] <= CurrentDate &&
    (ISBLANK(SELECTEDVALUE('BD Case'[Tag Type])) || 'BD Case'[Tag Type] = SELECTEDVALUE('BD Case'[Tag Type])) &&
    (ISBLANK(SELECTEDVALUE('BD Case'[Urgent Level])) || 'BD Case'[Urgent Level] = SELECTEDVALUE('BD Case'[Urgent Level])) &&
    (ISBLANK(SELECTEDVALUE('BD Case'[RankingforSlicer])) || 'BD Case'[RankingforSlicer] = SELECTEDVALUE('BD Case'[RankingforSlicer])) &&
    (ISBLANK(SELECTEDVALUE('BD Case'[departmentName])) || 'BD Case'[departmentName] = SELECTEDVALUE('BD Case'[departmentName])) &&
    (ISBLANK(SELECTEDVALUE('BD Case'[LocationParentName])) || 'BD Case'[LocationParentName] = SELECTEDVALUE('BD Case'[LocationParentName])) &&
    (ISBLANK(SELECTEDVALUE('BD Case'[internalSerial])) || 'BD Case'[internalSerial] = SELECTEDVALUE('BD Case'[internalSerial])) &&
    (ISBLANK(SELECTEDVALUE('BD Case'[Failure Type])) || 'BD Case'[Failure Type] = SELECTEDVALUE('BD Case'[Failure Type])) &&
    (ISBLANK(SELECTEDVALUE('BD Case'[repairType])) || 'BD Case'[repairType] = SELECTEDVALUE('BD Case'[repairType])) &&
    (ISBLANK(SELECTEDVALUE('BD Case'[responseByUser])) || 'BD Case'[responseByUser] = SELECTEDVALUE('BD Case'[responseByUser]))
    ),
    REMOVEFILTERS('BD Case'[ConvertedDate]) // Ensures cumulative count across all dates up to CurrentDate
    )

foodd's avatar
foodd
Icon for Community Champion rankCommunity Champion
2 years ago

Hello Nicharee2205 , and thank you for sharing a question with the Community.  The following is informational.  Please remember to adhere to the decorum of the Community Forum when asking a question.

Please provide your work-in-progress Power BI Desktop file (with sensitive information removed) that covers your issue or question completely in a usable format (not as a screenshot). You can upload the PBIX file to a cloud storage service such as OneDrive, Google Drive, Dropbox, or to a Github repository, and then share a file’s URL.

https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523/highlight/true#M607150

This allows members of the Forum to assess the state of the model, report layer, relationships, and any DAX applied.