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

[ahp-sig]

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

[ThxAlot · Accepted solution]

[ahp-sig]

This does not produce the desired results

Desired Result:

Actual result:

[JamesFR06]

Your period name came from your date table ?

[ahp-sig]

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

[JamesFR06]

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

[ahp-sig]

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

[JamesFR06]

Hi,

You are right sorry !!

Just add all(table) at the end like this

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,

all('Table')

)
RETURN
result

[JamesFR06]

HI

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
)