Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
Solved! Go to Solution.
Expertise = List.Accumulate( {Days as from Today}, {Skills and Knowledge}, (Current, Everyday) => Current & Day.LeanAndPractise(Everyday) ) |
This does not produce the desired results
Desired Result:
Actual result:
Your period name came from your date table ?
Period name is just FORMAT('Table'[Date],"mmm-yyyy"). See the pbix file https://drive.google.com/file/d/1n4QcTUZHKGKPurARPLTHczo4df5iybCG/view?usp=sharing
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
The result of your formula is not a cumulative sum. Its just a normal sum.
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,
)
RETURN
result
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
)
User | Count |
---|---|
57 | |
48 | |
18 | |
16 | |
15 |
User | Count |
---|---|
116 | |
42 | |
41 | |
28 | |
21 |