topic Re: Cumulative Sum by Category , year and month with missing values in data in DAX Commands and Tips

Cumulative Sum by Category , year and month with missing values in data

ahp-sig — Thu, 07 Mar 2024 22:48:19 GMT

How do you show cumulative sums by year that shows category even if its missing values in the source data for a time period?

For example: in the data below Product C is missing amounts in Feb-2023. The DAX measures i have tried so far are all excluding C from Feb cumulative results completely.

Source Data:

Date	Product	Amount
1/1/2023	A	1
1/1/2023	B	2
1/1/2023	C	1
1/2/2023	C	4
2/1/2023	A	9
2/2/2023	B	3
3/1/2023	A	4
3/1/2023	B	4
3/2/2023	C	3
1/1/2024	A	1
1/1/2024	B	1
1/1/2024	C	1
1/2/2024	A	3
1/2/2024	C	1
1/3/2024	B	4
2/1/2024	A	2
2/2/2024	C	1
2/2/2024	B	3
3/1/2024	A	9
3/1/2024	B	4
3/2/2024	C	3

What i have tried so far:

Measure = SUMX(FILTER(ALL('Table'), YEAR('Table'[Date])=YEAR(MAX('Table'[Date])) && MONTH('Table'[Date])<=MONTH(MAX('Table'[Date])) && 'Table'[Product]=MAX('Table'[Product])),'Table'[Amount])

I have also tried it with a calendar table with the same results

Measure 2 = var varDate = SELECTEDVALUE(CalendarTable[Date]) var varProduct = SELECTEDVALUE('Table'[Product]) return CALCULATE([SumAmount],CalendarTable[Date]<=varDate,'Table'[Product]=varProduct,VALUES(CalendarTable[Year]))

Result in each case does not show C in Feb-23:

Expected results:

PBIX file
https://drive.google.com/file/d/1n4QcTUZHKGKPurARPLTHczo4df5iybCG/view?usp=sharing

Re: Cumulative Sum by Category , year and month with missing values in data

JamesFR06 — Thu, 07 Mar 2024 22:32:01 GMT

Please fiond the measure :

Cumul =
VAR Product =
SELECTEDVALUE ( 'Tot Prod'[Product] )
VAR SelDate =
SELECTEDVALUE ( 'Tot Prod'[Date] )
VAR SelYear =
YEAR ( SelDate )
RETURN
CALCULATE (
SUM ( 'Tot Prod'[Amount] ),
'Tot Prod'[Date] <= SelDate
&& 'Tot Prod'[Product] = Product
&& YEAR ( 'Tot Prod'[Date] ) = SelYear
)

Re: Cumulative Sum by Category , year and month with missing values in data

ahp-sig — Thu, 07 Mar 2024 22:40:43 GMT

This does not produce the desired results

Desired Result:

Actual result:

Re: Cumulative Sum by Category , year and month with missing values in data

JamesFR06 — Thu, 07 Mar 2024 22:42:47 GMT

Your period name came from your date table ?

Re: Cumulative Sum by Category , year and month with missing values in data

ahp-sig — Thu, 07 Mar 2024 22:47:37 GMT

Period name is just FORMAT('Table'[Date],"mmm-yyyy"). See the pbix file https://drive.google.com/file/d/1n4QcTUZHKGKPurARPLTHczo4df5iybCG/view?usp=sharing

Re: Cumulative Sum by Category , year and month with missing values in data

JamesFR06 — Thu, 07 Mar 2024 23:00:58 GMT

Ok here it is

I have changed 2 columns on your matrice :

I choose product from Table

I choose Yearmonth from your calendar Table

MEASURE =
VAR Product =
SELECTEDVALUE ( 'Table'[Product] )
VAR SelDate =
SELECTEDVALUE ( CalendarTable[YearMonthnumber] ) //your table period name
//now searching from the last day for this period yyyymm
VAR Lastday =
MAXX (
FILTER ( CalendarTable, CalendarTable[YearMonthnumber] = SelDate ),
CalendarTable[Date]
)
VAR SelYear =
YEAR ( Lastday )
VAR result =
CALCULATE (
SUM ( 'Table'[Amount] ),
'Table'[Date] <= Lastday
&& 'Table'[Product] = Product
&& YEAR ( 'Table'[Date] ) = SelYear
)
RETURN
result

Re: Cumulative Sum by Category , year and month with missing values in data

ahp-sig — Thu, 07 Mar 2024 23:27:05 GMT

The result of your formula is not a cumulative sum. Its just a normal sum.

Re: Cumulative Sum by Category , year and month with missing values in data

JamesFR06 — Fri, 08 Mar 2024 07:14:14 GMT

Hi,

You are right sorry !!

Just add all(table) at the end like this

all('Table')

)
RETURN
result

Re: Cumulative Sum by Category , year and month with missing values in data

ThxAlot — Fri, 08 Mar 2024 16:01:44 GMT