Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
I have data lite this table. Year mont column and sales amount in each mont. I would like to calculate mothly cumulative value, but I couldn't do it. IS there any method to propose?
Solved! Go to Solution.
Hi @obesli,
You can try to use below measures:
Cumulative = VAR _current = SELECTEDVALUE ( 'Table'[DateKey] ) VAR _previous = MAXX ( FILTER ( ALL ( 'Table' ), [DateKey] < _current ), [DateKey] ) RETURN IF ( RIGHT ( VALUE ( _current ), 2 ) <> "01", SUMX ( FILTER ( ALL ( 'Table' ), [DateKey] < _current && LEFT ( VALUE ( [DateKey] ), 4 ) = LEFT ( VALUE ( _current ), 4 ) ), [Sales] ), MAX ( 'Table'[Sales] ) + LOOKUPVALUE ( 'Table'[Sales], 'Table'[DateKey], _previous ) ) Cumulative(Jan replace current + Previous Total) = VAR _current = SELECTEDVALUE ( 'Table'[DateKey] ) VAR _previous = MAXX ( FILTER ( ALL ( 'Table' ), [DateKey] < _current ), [DateKey] ) RETURN IF ( RIGHT ( VALUE ( _current ), 2 ) <> "01", SUMX ( FILTER ( ALL ( 'Table' ), [DateKey] < _current && LEFT ( VALUE ( [DateKey] ), 4 ) = LEFT ( VALUE ( _current ), 4 ) ), [Sales] ), MAX ( 'Table'[Sales] ) + SUMX ( FILTER ( ALL ( 'Table' ), 'Table'[DateKey] <= _previous && LEFT ( VALUE ( [DateKey] ), 4 ) = LEFT ( VALUE ( _previous ), 4 ) ), [Sales] ) )
Result:
Regards,
Xiaoxin Sheng
HI @obesli
You can use this calculated column
But this will Cumulate from all prior year.
Do you want accumulation to restart every year
= CALCULATE ( SUM ( [Total Sales] ), FILTER ( Table1, Table1[Year Month] <= EARLIER ( Table1[Year Month] ) ) )
You can try using this column if you Cumulative results to restart each year
= CALCULATE ( SUM ( [Total Sales] ), FILTER ( Table1, LEFT ( Table1[Year Month], 4 ) = LEFT ( EARLIER ( Table1[Year Month] ), 4 ) && Table1[Year Month] <= EARLIER ( Table1[Year Month] ) ) )
Hi,
Thank you for yoru reply. I did the second query and it works weel, but I need additional support.
I would like to summarize values before 201801 under 201801 and after 201901 under 201901.
How can I add such additional query?
Thanks,
I would like to show data like this.
HI @obesli
In that case you can add a colum to Group the periods
Then use this column for chart
For example
= IF ( Table1[Year Month] > 201901, "2019-2020", FORMAT ( Table1[Year Month], "General Number" ) )
I did it, but this time there are same values in different rows and all off them calculated in the cumulative value. I need to take only row value and the other thing is in 201901 I need to add the 201812 value to 201901.
How could I solve in the queries?
Hi @obesli,
You can try to use below measures:
Cumulative = VAR _current = SELECTEDVALUE ( 'Table'[DateKey] ) VAR _previous = MAXX ( FILTER ( ALL ( 'Table' ), [DateKey] < _current ), [DateKey] ) RETURN IF ( RIGHT ( VALUE ( _current ), 2 ) <> "01", SUMX ( FILTER ( ALL ( 'Table' ), [DateKey] < _current && LEFT ( VALUE ( [DateKey] ), 4 ) = LEFT ( VALUE ( _current ), 4 ) ), [Sales] ), MAX ( 'Table'[Sales] ) + LOOKUPVALUE ( 'Table'[Sales], 'Table'[DateKey], _previous ) ) Cumulative(Jan replace current + Previous Total) = VAR _current = SELECTEDVALUE ( 'Table'[DateKey] ) VAR _previous = MAXX ( FILTER ( ALL ( 'Table' ), [DateKey] < _current ), [DateKey] ) RETURN IF ( RIGHT ( VALUE ( _current ), 2 ) <> "01", SUMX ( FILTER ( ALL ( 'Table' ), [DateKey] < _current && LEFT ( VALUE ( [DateKey] ), 4 ) = LEFT ( VALUE ( _current ), 4 ) ), [Sales] ), MAX ( 'Table'[Sales] ) + SUMX ( FILTER ( ALL ( 'Table' ), 'Table'[DateKey] <= _previous && LEFT ( VALUE ( [DateKey] ), 4 ) = LEFT ( VALUE ( _previous ), 4 ) ), [Sales] ) )
Result:
Regards,
Xiaoxin Sheng
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
90 | |
88 | |
83 | |
64 | |
49 |
User | Count |
---|---|
127 | |
108 | |
87 | |
70 | |
66 |