topic Re: Cumulative sum and calendar relation in DAX Commands and Tips

Cumulative sum and calendar relation

Anonymous — Mon, 19 Aug 2024 13:07:00 GMT

Hello,

I have a problem on my report. I created a measure to have a cumulative column and it works.

But i want to see the weaks where i don't have data, in my exemple i have 3 closed actions :
1 action on weak 7; 1 on weak 10 and 1 on weak 11. The sum works just fine because i see 3 actions on weak 11

But in my graph i would like to have the sum even for the weaks without new actions, so in weak 8 and 9 i want to see 1 closed action.

Here's what my data test look like :

N°	Date	Origine	Rédacteur du point	Domaine	IDA	Responsable si action	Référent	Contributeur	Détails	Date Réalisation souhaitée	Statut	Bloquant pour le blueprint	Présent dans le BluePrint	Date de cloture	Intégré BP Consultant O/N	Présence BlueP	Historique/Résultat	à balayer prochain suivi	Point majeur (1/0)	Nombre de report d'action	Ouvert ou fermé	Espérance de vie	Retard & ouvert	Date YYYYWW	Date WW
1	2023-02-16	Référentiels	OTA	06 - Référentiel	Action	GO	OTA			2024-05-31	Fermé			2024-07-08						0	0	470	0	202307	7
2	2023-03-10	Processus	BPO	01 - Transverse	Action	GO	BPO			2024-06-30	Fermé			2024-07-08						0	0	478	0	202310	10
3	2024-03-16	Conduite de changement	BPO	01 - Transverse	Action	GO	ADE			2024-06-30	Fermé			2024-07-08						0	0	106	0	202411	11
4	2024-05-13	bleep - étudier entre Blueplanner et Infor	BPO	04 - Accords Commerciaux	Action	GO	LGU	KCA		2024-09-30	A faire									0	1	140	0	202420	20
5	2024-05-13	PIM - statuer sur INFOR PLM ou maintien d'agena	LGU	06 - Référentiel	Action	GO	LGU	COPIL/MBE/GLA		2024-09-30	A faire									0	1	140	0	202420	20
6	2024-05-13	Réceptions - statuer sur positionnement VIF ou Infor	BPO	03 - Achats/ Appros/ Logistique	Action	GO	LGU	BPO		2024-09-30	En cours									0	1	140	0	202420	20

And this is what my measure look like on PBI :

Cumul Actions par Semaine =

CALCULATE(

[Nombre d'Actions],

FILTER(

ALL('Feuil1'[Date WW]),

'Feuil1'[Date WW] <= MAX('Feuil1'[Date WW])

)

And the graph look like this after :

I want to have something like that :

I hope that's clear, i give you a link of the 2 file here

PBI : test.pbix

Excel : données.xlsx

And my other question is that I created 2 measures :

Créées par mois =

CALCULATE(

COUNT('Feuil1'[Date]),

USERELATIONSHIP('Calendrier'[Date], 'Feuil1'[Date])

)

And

Terminées dans le mois =

CALCULATE(

COUNT('Feuil1'[Date de cloture]),

USERELATIONSHIP('Calendrier'[Date], 'Feuil1'[Date de cloture])

)

Based on the relationship with my calendar table, it is a good way to do it ? Do i really need a calendar table or can i do it with something else ?

Re: Cumulative sum and calendar relation

Greg_Deckler — Mon, 19 Aug 2024 11:09:01 GMT

Anonymous Would need to know more about your setup. If you can post sample data as text or a link to your PBIX file (OneDrive, Box, etc.) would help greatly.

Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

Re: Cumulative sum and calendar relation

Anonymous — Mon, 19 Aug 2024 13:03:14 GMT

Hello Greg,

I change my first post so it's easier to understand

Re: Cumulative sum and calendar relation

Greg_Deckler — Mon, 19 Aug 2024 13:17:00 GMT

Anonymous You can fix this with a disconnected table and a measure:

Table = GENERATESERIES( MIN( 'Feuil1'[Date WW] ), MAX( 'Feuil1'[Date WW] ), 1 ) Measure = VAR __MaxDate = MAX( 'Table'[Value] ) VAR __Table = FILTER( ALL( 'Feuil1' ), [Date WW] <= __MaxDate ) VAR __Result = COUNTROWS( __Table ) RETURN __Result

PBIX is attached below signature.

Re: Cumulative sum and calendar relation

Greg_Deckler — Mon, 19 Aug 2024 22:28:25 GMT

Anonymous One small correction to this, the measure should be this:

Measure = VAR __Category = MAX( 'Feuil1'[Statut] ) VAR __MaxDate = MAX( 'Table'[Value] ) VAR __Table = FILTER( ALL( 'Feuil1' ), [Date WW] <= __MaxDate && [Statut] = __Category ) VAR __Result = COUNTROWS( __Table ) RETURN __Result

Re: Cumulative sum and calendar relation

Anonymous — Tue, 20 Aug 2024 07:51:48 GMT

Hello Greg, yes it works with your exemple thank you.
I tried to use the same logic with a real calendar table linked to my fact table "Feuil1". But it doesn't work, i still have some weaks empty in the graph. I was thinking about using total ytd, but i'm probably not using it right because it doesn't work neither

Re: Cumulative sum and calendar relation

95uey — Mon, 17 Feb 2025 01:57:11 GMT

I have a similar problem....