The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
Hi everyone,
need help to create cumulative total DAX column or even a Mcode , but i dont need a measure
where 2019-2022 should be blank/null
2023=2022
2024=2022+2023
2025=2022+2023+2024 and so on till 2030 for each BU
BU | Year | reduction | Index |
ADD | 2019 | 0 | |
ADD | 2020 | 1 | |
ADD | 2021 | 2 | |
ADD | 2022 | -4.3 | 3 |
ADD | 2023 | -1.7 | 4 |
ADD | 2024 | -16 | 5 |
ADD | 2025 | 7 | 6 |
ADD | 2026 | -2.6 | 7 |
ADD | 2027 | -2 | 8 |
ADD | 2028 | -5 | 9 |
ADD | 2029 | -10 | 10 |
ADD | 2030 | -2 | 11 |
CA | 2019 | 12 | |
CA | 2020 | 13 | |
CA | 2021 | 14 | |
CA | 2022 | -18 | 15 |
CA | 2023 | -16.81 | 16 |
CA | 2024 | -7.89 | 17 |
CA | 2025 | -18 | 18 |
CA | 2026 | -9.25 | 19 |
CA | 2027 | -2 | 20 |
CA | 2028 | -7.5 | 21 |
CA | 2029 | 0 | 22 |
CA | 2030 | 0 | 23 |
Solved! Go to Solution.
@amitchandak I got the solution,
i created below column
@snaraya , Create a separate year column and join with year
and try a column like
calculate(Sum(Table[reduction]), filter(all(year), Year[Year] <= max(Year[Year]) && Year[Year]>=2022) )
You can try window function
Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc
Hi @amitchandak ,
Thanks for reply,
i am confused a little now, i created a new table with Year and created a relationship between both table. and after using your formula in a column it gives me same column as reduction.
What am i missing here exactly
also when i tried creating a cumulative using window function it gives circular dependency error
what exactly do you mean when you say join with year
Relationship
@amitchandak I got the solution,
i created below column
User | Count |
---|---|
16 | |
8 | |
7 | |
6 | |
6 |
User | Count |
---|---|
26 | |
13 | |
12 | |
8 | |
8 |