Cumulative count by categories (Cumulative Flow Diagram)

[arturoclx]

I need to create a Cumulative Flow Diagram, so I have the start date for each Status and I need to calculate the Cumulative count for each category. 

So I have the count for each category by date but I need the cumulative, for example in Aug/2020 I have 63 lines, in Sep/2020 I have 93 lines, so in Sep/2020 should be 63+93 = 156

I tried using the formula but it came out wrong:

CFD2 = CALCULATE(

    COUNT(History[History Status]),

    FILTER(

        ALL(History),

        History[Status start]<=MAX(History[Status start])

    )

)

History[History Status] = Column with the categories
History = table name
History[Status start] = Column with my dates

[arturoclx · Accepted solution]

Actually I was having problems to make my count measure, so I found this solution:

CFD =

var calendarCFD = CALENDAR(

    MIN(History[History New Value Start]),

    TODAY()

)

var cfd_base_table = ADDCOLUMNS(

    calendarCFD,

    "Backlog",0,

    "To Do", 0,

    "In Progress", 0,

    "Waiting Code Review", 0,

    "Pull Request", 0,

    "Code Review", 0,

    "Under Analysis", 0,

    "Waiting to Test", 0,

    "Test", 0,

    "Waiting for Delivery", 0,

    "Implementing / Delivery", 0,

    "Done", 0

)

var cfd_table = GROUPBY(

    UNION(

        cfd_base_table,

        GROUPBY(

            ADDCOLUMNS(

                GROUPBY(

                    SELECTCOLUMNS(

                        FILTER(

                            History,

                            History[History New Value] = "Backlog"

                        ),

                        "Status", History[History New Value],

                        "Data", FORMAT(History[History New Value Start],"dd/MM/YYYY"),

                        "Key", History[Key]

                    ),

                    [Key],

                    "Date",MINX(CURRENTGROUP(),[Data]),

                    "Status", MAXX(CURRENTGROUP(),[Status])

                ),

                "Backlog",1,

                "To Do", 0,

                "In Progress", 0,

                "Waiting Code Review", 0,

                "Pull Request", 0,

                "Code Review", 0,

                "Under Analysis", 0,

                "Waiting to Test", 0,

                "Test", 0,

                "Waiting for Delivery", 0,

                "Implementing / Delivery", 0,

                "Done", 0

            ),

            [Date],

            "Backlog",SUMX(CURRENTGROUP(), [Backlog]),

            "To Do", SUMX(CURRENTGROUP(), [To Do]),

            "In Progress", SUMX(CURRENTGROUP(), [In Progress]),

            "Waiting Code Review", SUMX(CURRENTGROUP(), [Waiting Code Review]),

            "Pull Request", SUMX(CURRENTGROUP(), [Pull Request]),

            "Code Review", SUMX(CURRENTGROUP(), [Code Review]),

            "Under Analysis", SUMX(CURRENTGROUP(), [Under Analysis]),

            "Waiting to Test", SUMX(CURRENTGROUP(), [Waiting to Test]),

            "Test", SUMX(CURRENTGROUP(), [Test]),

            "Waiting for Delivery", SUMX(CURRENTGROUP(), [Waiting for Delivery]),

            "Implementing / Delivery", SUMX(CURRENTGROUP(), [Implementing / Delivery]),

            "Done", SUMX(CURRENTGROUP(), [Done])

        ),

        GROUPBY(

            ADDCOLUMNS(

                GROUPBY(

                    SELECTCOLUMNS(

                        FILTER(

                            History,

                            History[History New Value] = "To Do"

                        ),

                        "Status", History[History New Value],

                        "Data", FORMAT(History[History New Value Start],"dd/MM/YYYY"),

                        "Key", History[Key]

                    ),

                    [Key],

                    "Date",MINX(CURRENTGROUP(),[Data]),

                    "Status", MAXX(CURRENTGROUP(),[Status])

                ),

                "Backlog", 0,

                "To Do", 1,

                "In Progress", 0,

                "Waiting Code Review", 0,

                "Pull Request", 0,

                "Code Review", 0,

                "Under Analysis", 0,

                "Waiting to Test", 0,

                "Test", 0,

                "Waiting for Delivery", 0,

                "Implementing / Delivery", 0,

                "Done", 0

            ),

            [Date],

            "Backlog",SUMX(CURRENTGROUP(), [Backlog]),

            "To Do", SUMX(CURRENTGROUP(), [To Do]),

            "In Progress", SUMX(CURRENTGROUP(), [In Progress]),

            "Waiting Code Review", SUMX(CURRENTGROUP(), [Waiting Code Review]),

            "Pull Request", SUMX(CURRENTGROUP(), [Pull Request]),

            "Code Review", SUMX(CURRENTGROUP(), [Code Review]),

            "Under Analysis", SUMX(CURRENTGROUP(), [Under Analysis]),

            "Waiting to Test", SUMX(CURRENTGROUP(), [Waiting to Test]),

            "Test", SUMX(CURRENTGROUP(), [Test]),

            "Waiting for Delivery", SUMX(CURRENTGROUP(), [Waiting for Delivery]),

            "Implementing / Delivery", SUMX(CURRENTGROUP(), [Implementing / Delivery]),

            "Done", SUMX(CURRENTGROUP(), [Done])

        ),

// repeat this part for each category status you have

[Date],

    "Backlog",SUMX(CURRENTGROUP(), [Backlog]),

    "To Do", SUMX(CURRENTGROUP(), [To Do]),

    "In Progress", SUMX(CURRENTGROUP(), [In Progress]),

    "Waiting Code Review", SUMX(CURRENTGROUP(), [Waiting Code Review]),

    "Pull Request", SUMX(CURRENTGROUP(), [Pull Request]),

    "Code Review", SUMX(CURRENTGROUP(), [Code Review]),

    "Under Analysis", SUMX(CURRENTGROUP(), [Under Analysis]),

    "Waiting to Test", SUMX(CURRENTGROUP(), [Waiting to Test]),

    "Test", SUMX(CURRENTGROUP(), [Test]),

    "Waiting for Delivery", SUMX(CURRENTGROUP(), [Waiting for Delivery]),

    "Implementing / Delivery", SUMX(CURRENTGROUP(), [Implementing / Delivery]),

    "Done", SUMX(CURRENTGROUP(), [Done])

)

return cfd_table

Then, I created the measure for the acumulate counts (one measure for each category status)

CM_Backlog = CALCULATE(

    SUM(CFD[Backlog]),

    FILTER(

        ALL(CFD),

        CFD[Date]<=MAX(CFD[Date])

    )

)

[Ashish_Mathur]

Hi,

Create a Calendar Table and write calculated column formulas to extract Year, Month name and Month number.  Sort the Month name by the Month number.  To your visual, drag Year and Month name from the Calendar Table.  Assuming Count is a measure that you have already written, write this measure.

Running count = calculate([count],datesytd(calendar[date],"31/12"))

Hope this helps.

[amitchandak]

@arturoclx , Join start Date with date of a date Table and try like

CFD2 = CALCULATE(
COUNT(History[History Status]),
FILTER(
ALL(Date),
Date[Date]<=MAX(Date[Date])
)
)

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5b...
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.