Cumulative Count by Week of Year

[Anonymous]

Hello Forum members,

I am new to Power BI reporting & creating DAX formulas to produce some reporting.   With that said we would like to create a Cumulative stack column chart based on weekly progress using column “Class” as legend, “Week of Year” on the Axis and value based on “ID ” row count in Implementation table.

I've search similar Cumulative examples posted within this forum but unlucky for me those solutions found does not seems to work for my situation.

Any help or advice would be greatly appreciated,

Thanks you

Don

ID	Class	SCHEDULED_END_DATE	Week of Year	Year
61488	Approved	9/11/2019 0:00	37	2019
61381	Rejected	10/13/2019 0:00	42	2019
61485	Hold	3/10/2019 0:00	11	2019
61345	Standard	1/17/2019 0:00	3	2019
61683	Normal	11/20/2019 0:00	47	2019
61551	Normal	8/3/2019 0:00	31	2019
61482	Normal	12/26/2019 0:00	52	2019
61352	Expedited	9/13/2019 0:00	37	2019
61678	Emergency	6/24/2019 0:00	26	2019
61689	Expedited	12/1/2019 0:00	49	2019
61590	Emergency	9/15/2019 0:00	38	2019
61360	Expedited	10/13/2019 0:00	42	2019
61690	Approved	10/16/2019 0:00	42	2019
61698	Rejected	12/8/2019 0:00	50	2019

[v-lid-msft · Accepted solution]

Hi @Anonymous ,

We can try to use this measure as the value field.

value =
COUNTROWS (
    FILTER (
        ALL ( 'Table' ),
        [Week of Year] <= MAX ( 'Table'[Week of Year] )
            && [Class] IN FILTERS ( 'Table'[Class] )
    )
)

If it doesn't meet your requirement, Please show the exact expected result based on the Tables that you have shared.

BTW, pbix as attached.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-lid-msft · Accepted solution]

Hi @Anonymous ,

We can try to use this measure as the value field.

value =
COUNTROWS (
    FILTER (
        ALL ( 'Table' ),
        [Week of Year] <= MAX ( 'Table'[Week of Year] )
            && [Class] IN FILTERS ( 'Table'[Class] )
    )
)

If it doesn't meet your requirement, Please show the exact expected result based on the Tables that you have shared.

BTW, pbix as attached.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

Dong Li, thank you very much your solution worked great the first time - I would not have been able too resolved this issue without your help & this forum.

Best regards,

Don

[Greg_Deckler]

See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

Also, I have written a bunch of week based quick measures:

https://community.powerbi.com/t5/forums/searchpage/tab/message?advanced=false&allow_punctuation=fals...

You also might find Sequential useful:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Sequential/m-p/380231?search-action-id=87631...