Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Input:
Label | Date | M | P | $ |
PD | 3/2/2024 | 4406 | S7 | -100 |
PD | 3/3/2024 | 5102 | S7 | 100 |
PD | 3/4/2024 | 6181 | S0 | 200 |
PD | 3/5/2024 | 7161 | S0 | 500 |
PD | 3/6/2024 | 7385 | S0 | -300 |
PD | 3/7/2024 | 9587 | S0 | 200 |
ST | 3/8/2024 | 1000 | S7 | 400 |
3/9/2024 | 3/9/2024 | 1001 | S7 | 900 |
3/10/2024 | 3/10/2024 | 1201 | S7 | 600 |
3/11/2024 | 3/11/2024 | 1201 | S7 | 700 |
3/12/2024 | 3/12/2024 | 1201 | S7 | 500 |
3/13/2024 | 3/13/2024 | 1201 | S7 | -100 |
Output:
Label | Date | M | P | $ | Running Total | |
PD | 3/2/2024 | 4406 | S7 | -100 | -100 | =E2 |
PD | 3/3/2024 | 5102 | S7 | 100 | 0 | =F2+E3 |
PD | 3/4/2024 | 6181 | S0 | 200 | 200 | =F3+E4 |
PD | 3/5/2024 | 7161 | S0 | 500 | 700 | =F4+E5 |
PD | 3/6/2024 | 7385 | S0 | -300 | 400 | =F5+E6 |
PD | 3/7/2024 | 9587 | S0 | 200 | 600 | =F6+E7 |
ST | 3/8/2024 | 1000 | S7 | 400 | 400 | =E8 |
3/9/2024 | 3/9/2024 | 1001 | S7 | 900 | 900 | =E9 |
3/10/2024 | 3/10/2024 | 1201 | S7 | 600 | 1500 | =F9+E10 |
3/11/2024 | 3/11/2024 | 1201 | S7 | 700 | 2200 | =F10+E11 |
3/12/2024 | 3/12/2024 | 1201 | S7 | 500 | 2700 | =F11+E12 |
3/13/2024 | 3/13/2024 | 1201 | S7 | -100 | 2600 | =F12+E13 |
Also, want to able to slice based on columns M, P, Label etc.
Apprciate any help
Solved! Go to Solution.
Hi @rp2022 ,
Please create a new calculated column:
Group =
VAR __cur_label = 'Table'[Label]
VAR __group = IF(ISERROR(VALUE(__cur_label)),__cur_label,"Date")
RETURN
__group
And then please create a new measure:
Running Total =
VAR __group = MAX('Table'[Group])
VAR __cur_date = MAX('Table'[Date])
VAR __result = CALCULATE(SUM('Table'[$]),FILTER(ALLSELECTED('Table'),'Table'[Group]=__group && 'Table'[Date]<=__cur_date))
RETURN
__result
Output:
Best Regards,
Gao
Community Support Team
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data in the Power BI Forum -- China Power BI User Group
Hi @rp2022 ,
Please create a new calculated column:
Group =
VAR __cur_label = 'Table'[Label]
VAR __group = IF(ISERROR(VALUE(__cur_label)),__cur_label,"Date")
RETURN
__group
And then please create a new measure:
Running Total =
VAR __group = MAX('Table'[Group])
VAR __cur_date = MAX('Table'[Date])
VAR __result = CALCULATE(SUM('Table'[$]),FILTER(ALLSELECTED('Table'),'Table'[Group]=__group && 'Table'[Date]<=__cur_date))
RETURN
__result
Output:
Best Regards,
Gao
Community Support Team
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data in the Power BI Forum -- China Power BI User Group
Works like a charm, thank you so much
Hi @rp2022
You can use the DAX measure :
The pbix is attached
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
User | Count |
---|---|
98 | |
91 | |
84 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
66 |