Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
Hi all,
I have following table
year | month | sales |
2024 | january | 10 |
2024 | february | 20 |
2024 | march | 30 |
2024 | april | 40 |
2024 | may | 50 |
2024 | june | 60 |
2024 | july | 70 |
2024 | august | 80 |
2024 | september | 90 |
2024 | october | 100 |
2024 | november | 110 |
2024 | december | 120 |
2025 | january | 15 |
2025 | february | 20 |
2025 | march | 534 |
2025 | april | 81 |
2025 | may | 153 |
2025 | june | 32 |
2025 | july | 78 |
2025 | august | 4254 |
2025 | september | 12 |
2025 | october | 45 |
2025 | november | 12 |
2025 | december | 454 |
What I want to achieve with DAX is to calculate Cumulative values per each month within the year. But I want to keep January as is, so the sum will start from february.
So:
January = January
February = January + February
March = February + March
etc....
I want to achieve this:
year | month | sales | Cumulative |
2024 | january | 10 | 10 |
2024 | february | 20 | 30 |
2024 | march | 30 | 60 |
2024 | april | 40 | 100 |
2024 | may | 50 | 150 |
2024 | june | 60 | 210 |
2024 | july | 70 | 280 |
2024 | august | 80 | 360 |
2024 | september | 90 | 450 |
2024 | october | 100 | 550 |
2024 | november | 110 | 660 |
2024 | december | 120 | 780 |
2025 | january | 15 | 15 |
2025 | february | 20 | 35 |
2025 | march | 534 | 569 |
2025 | april | 81 | 650 |
2025 | may | 153 | 803 |
2025 | june | 32 | 835 |
2025 | july | 78 | 913 |
2025 | august | 4254 | 5167 |
2025 | september | 12 | 5179 |
2025 | october | 45 | 5224 |
2025 | november | 12 | 5236 |
2025 | december | 454 | 5690 |
Solved! Go to Solution.
Hi,
I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.
Please check the below picture and the attached pbix file.
Sales: =
SUM(sales[sales])
WINDOW function (DAX) - DAX | Microsoft Learn
Cumulative sales: =
CALCULATE (
[Sales:],
WINDOW (
1,
ABS,
0,
REL,
ALL ( 'calendar'[Year], 'calendar'[Month name], 'calendar'[Month number] ),
ORDERBY ( 'calendar'[Month number], ASC ),
,
PARTITIONBY ( 'calendar'[Year] )
)
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi @martysk - create a new column as below
MonthOrder =
SWITCH(
cummunl[month],
"january", 1,
"february", 2,
"march", 3,
"april", 4,
"may", 5,
"june", 6,
"july", 7,
"august", 8,
"september", 9,
"october", 10,
"november", 11,
"december", 12
)
Now update your Cumulative measure to use the new MonthOrder column for proper comparisons
Proud to be a Super User! | |
Hi,
I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.
Please check the below picture and the attached pbix file.
Sales: =
SUM(sales[sales])
WINDOW function (DAX) - DAX | Microsoft Learn
Cumulative sales: =
CALCULATE (
[Sales:],
WINDOW (
1,
ABS,
0,
REL,
ALL ( 'calendar'[Year], 'calendar'[Month name], 'calendar'[Month number] ),
ORDERBY ( 'calendar'[Month number], ASC ),
,
PARTITIONBY ( 'calendar'[Year] )
)
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
142 | |
76 | |
63 | |
51 | |
47 |
User | Count |
---|---|
211 | |
82 | |
61 | |
61 | |
60 |