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
    )

BeaBF's avatar
BeaBF
Icon for Super User rankSuper User
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
)

  • Nicharee2205's avatar
    Nicharee2205
    Frequent Visitor
    2 years ago

    Thankyou, actually I also try this:

    and it works too