Forum Discussion

Mourt's avatar
Mourt
Icon for Helper I rankHelper I
3 years ago
Solved

Cumulative Completion Rate with Month Slicer

Hi everyone, I'm trying to calculate cumulative completion rate by all users over moths, the issue is that in the below table for ex when I filter on october it divides users who finished till oct...
Need Help
  • Mikelytics's avatar
    Mikelytics
    3 years ago

    Hi Mourt 

     

    Please find below an appraoch:

     

    1) base table (completion status is type "whole number")

     

     

    2) Data model

     

    3) Measure and result

     

     

    Completion% = 

var var_RollingCompletion =
    CALCULATE(
        SUM(CompletionRate[Completion_status]),
        ALL(DimDate),
        DimDate[Date]<= MAX(DimDate[Date])
    ) 

var var_AllItems = 
    CALCULATE(
        COUNTROWS(CompletionRate),
        ALL(CompletionRate)
    )

RETURN
DIVIDE(var_RollingCompletion, var_AllItems, BLANK())

     

    Best regards

    Michael

    -----------------------------------------------------

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your thumbs up!

    @ me in replies or I'll lose your thread.

     

     

     

     

     

     

     

Mourt's avatar
Mourt
Icon for Helper I rankHelper I
3 years ago

Thanks for the solution, works perfectly, just did a slight modification as Users belong to different departments

Completion% = 

var var_RollingCompletion =
    CALCULATE(
        SUM(CompletionRate[Completion_status]),
        ALL(DimDate),
        DimDate[Date]<= MAX(DimDate[Date])
    ) 

var var_AllItems = 
    CALCULATE(
        COUNTROWS(CompletionRate),
        ALL(CompletionRate)
    )

RETURN
DIVIDE(var_RollingCompletion, var_AllItems, BLANK())
Mikelytics's avatar
Mikelytics
Icon for Resident Rockstar rankResident Rockstar
3 years ago

Mourt  Aweseom! Thank you for the feedback! 🙂