Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello,
the raw data format is like this table below.
Type | Weeknum | Hit count |
A | 1 | 1 |
A | 2 | 1 |
A | 3 | 1 |
A | 8 | 1 |
A | 9 | 1 |
A | 12 | 1 |
B | 1 | 0 |
B | 3 | 1 |
... | ... | ... |
for this kind of table, I would like to get cumlative sum of 13 weeks
so I made a dax formula using sum() and filter()
However the result is below
<cumlative sum of 13 weeks>
Weeknum | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | ... |
A | 1 | 2 | 3 | 3 | 4 | ||||
B | 0 | 1 |
I guess it's becuase for A dose not have weeknum 5,6,7
it does not return anything.
What I would like to get is... below
Weeknum | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | ... |
A | 1 | 2 | 3 | 3 | 3 | 3 | 3 | 4 | |
B | 0 | 1 | 1 |
Could you help me please?
Solved! Go to Solution.
Hi, @zzzsou
You can try the following methods. Create a new Weeknum table as a column of the matrix.
Measure = CALCULATE(SUM('Table'[Hit count]),FILTER(ALL('Weeknum'),[Weeknum]<=SELECTEDVALUE('Weeknum'[Weeknum])))
Is this the result you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank your for your reply! I applied it to my model and it works very well!!😃
Hi, @zzzsou
You can try the following methods. Create a new Weeknum table as a column of the matrix.
Measure = CALCULATE(SUM('Table'[Hit count]),FILTER(ALL('Weeknum'),[Weeknum]<=SELECTEDVALUE('Weeknum'[Weeknum])))
Is this the result you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
User | Count |
---|---|
92 | |
85 | |
76 | |
66 | |
62 |
User | Count |
---|---|
110 | |
96 | |
95 | |
64 | |
57 |