Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Can you save me ?
I want to calulate culmulative average in DAX ?
Can you make the "orange color table " by using Dax ?
IDDate
A | 2022-07-10 |
B | 2022-07-10 |
C | 2022-07-15 |
D | 2022-07-15 |
E | 2022-07-15 |
F | 2022-07-15 |
G | 2022-07-20 |
H | 2022-07-21 |
I | 2022-07-21 |
J | 2022-07-24 |
K | 2022-08-10 |
L | 2022-08-11 |
M | 2022-08-15 |
N | 2022-08-20 |
O | 2022-08-20 |
P | 2022-08-20 |
Q | 2022-08-20 |
R | 2022-09-10 |
S | 2022-09-10 |
T | 2022-09-20 |
U | 2022-09-20 |
V | 2022-09-30 |
W | 2022-09-30 |
X | 2022-10-05 |
Y | 2022-10-10 |
Z | 2022-10-20 |
TableB
CategoryApplyDateGap
xxx | 2022-07-18 | 0.3 |
yyy | 2022-07-30 | 0.1 |
zzz | 2022-08-18 | 0.7 |
aaa | 2022-08-31 | 0.9 |
Table C (calendar )
DateMonth
2022-07-01 | 7 |
2022-07-02 | 7 |
2022-07-03 | 7 |
2022-07-04 | 7 |
2022-07-05 | 7 |
2022-07-06 | 7 |
2022-07-07 | 7 |
2022-07-08 | 7 |
2022-07-09 | 7 |
2022-07-10 | 7 |
2022-07-11 | 7 |
2022-07-12 | 7 |
2022-07-13 | 7 |
2022-07-14 | 7 |
2022-07-15 | 7 |
2022-07-16 | 7 |
2022-07-17 | 7 |
2022-07-18 | 7 |
2022-07-19 | 7 |
2022-07-20 | 7 |
2022-07-21 | 7 |
2022-07-22 | 7 |
2022-07-23 | 7 |
2022-07-24 | 7 |
2022-07-25 | 7 |
2022-07-26 | 7 |
2022-07-27 | 7 |
2022-07-28 | 7 |
2022-07-29 | 7 |
2022-07-30 | 7 |
2022-07-31 | 7 |
2022-08-01 | 8 |
2022-08-02 | 8 |
2022-08-03 | 8 |
2022-08-04 | 8 |
2022-08-05 | 8 |
2022-08-06 | 8 |
2022-08-07 | 8 |
2022-08-08 | 8 |
2022-08-09 | 8 |
2022-08-10 | 8 |
2022-08-11 | 8 |
2022-08-12 | 8 |
2022-08-13 | 8 |
2022-08-14 | 8 |
2022-08-15 | 8 |
2022-08-16 | 8 |
2022-08-17 | 8 |
2022-08-18 | 8 |
2022-08-19 | 8 |
2022-08-20 | 8 |
2022-08-21 | 8 |
2022-08-22 | 8 |
2022-08-23 | 8 |
2022-08-24 | 8 |
2022-08-25 | 8 |
2022-08-26 | 8 |
2022-08-27 | 8 |
2022-08-28 | 8 |
2022-08-29 | 8 |
2022-08-30 | 8 |
2022-08-31 | 8 |
2022-09-01 | 9 |
2022-09-02 | 9 |
2022-09-03 | 9 |
2022-09-04 | 9 |
2022-09-05 | 9 |
2022-09-06 | 9 |
2022-09-07 | 9 |
2022-09-08 | 9 |
2022-09-09 | 9 |
2022-09-10 | 9 |
2022-09-11 | 9 |
2022-09-12 | 9 |
2022-09-13 | 9 |
2022-09-14 | 9 |
2022-09-15 | 9 |
2022-09-16 | 9 |
2022-09-17 | 9 |
2022-09-18 | 9 |
2022-09-19 | 9 |
2022-09-20 | 9 |
2022-09-21 | 9 |
2022-09-22 | 9 |
2022-09-23 | 9 |
2022-09-24 | 9 |
2022-09-25 | 9 |
2022-09-26 | 9 |
2022-09-27 | 9 |
2022-09-28 | 9 |
2022-09-29 | 9 |
2022-09-30 | 9 |
2022-10-01 | 10 |
2022-10-02 | 10 |
2022-10-03 | 10 |
2022-10-04 | 10 |
2022-10-05 | 10 |
2022-10-06 | 10 |
2022-10-07 | 10 |
2022-10-08 | 10 |
2022-10-09 | 10 |
2022-10-10 | 10 |
2022-10-11 | 10 |
2022-10-12 | 10 |
2022-10-13 | 10 |
2022-10-14 | 10 |
2022-10-15 | 10 |
2022-10-16 | 10 |
2022-10-17 | 10 |
2022-10-18 | 10 |
2022-10-19 | 10 |
2022-10-20 | 10 |
2022-10-21 | 10 |
2022-10-22 | 10 |
2022-10-23 | 10 |
2022-10-24 | 10 |
2022-10-25 | 10 |
2022-10-26 | 10 |
2022-10-27 | 10 |
2022-10-28 | 10 |
2022-10-29 | 10 |
2022-10-30 | 10 |
2022-10-31 | 10 |
Month | MonthQty | Culmulative Qty | Culmulative SUM | Culmulate Avg. |
7 | 10 | 10 | 1.2 | 0.12 |
8 | 7 | 17 | 6.8 | 0.40 |
9 | 6 | 23 | 18.8 | 0.82 |
10 | 3 | 26 | 24.8 | 0.95 |
Solved! Go to Solution.
To simplyfy the solution to you problem I would suggest to model your data with a new Fact table that combines the information in TableA with TableB.
So if the Date in TableA is on or after the ApplyDate in TableB you get the Category and corresponding Gap in separate columns in the new Fact table.
You can create this Fact table as a new calculated table with the following DAX expression:
To simplyfy the solution to you problem I would suggest to model your data with a new Fact table that combines the information in TableA with TableB.
So if the Date in TableA is on or after the ApplyDate in TableB you get the Category and corresponding Gap in separate columns in the new Fact table.
You can create this Fact table as a new calculated table with the following DAX expression:
You are my super hero!!
where did the QTY and SUM come from? and what's your data structure?
Thank you for your reply
This is excel file.
I simply expressed the direction I wanted in Excel as a table. It would be nice if the files were attached...
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
25 | |
19 | |
18 | |
18 | |
15 |
User | Count |
---|---|
39 | |
22 | |
18 | |
15 | |
12 |