Forum Discussion
Post ProdigyCumulative total until the selected month
I need to calculate the cumulative total until the selected month. Below is my table.
| Month | Size | Cumulative Size |
| Jan-19 | 69 | 53 |
| Feb-19 | 63 | 116 |
| Mar-19 | 85 | 201 |
| Apr-19 | 63 | 264 |
| May-19 | 62 | 326 |
| Jun-19 | 96 | 422 |
| Jul-19 | 77 | 499 |
| Aug-19 | 84 | 583 |
| Sep-19 | 62 | 645 |
| Oct-19 | 83 | 728 |
| Nov-19 | 53 | 781 |
| Dec-19 | 84 | 865 |
Expected Output:
When I use a filter for Month and select Sep-19, I should get the below:
| Month | Size | Cumulative Size |
| Jan-19 | 52 | 53 |
| Feb-19 | 74 | 127 |
| Mar-19 | 65 | 192 |
| Apr-19 | 89 | 281 |
| May-19 | 94 | 375 |
| Jun-19 | 50 | 425 |
| Jul-19 | 94 | 519 |
| Aug-19 | 95 | 614 |
| Sep-19 | 100 | 714 |
hi Kolumam
For your case, you must use a spreated Month tables as the slicer
Dim Month = VALUES('Table'[Month])Then use this measure in your visual.
Measure = IF(MAX('Table'[Month])<=MAX('Dim Month'[Month]),CALCULATE(SUM('Table'[Size]),FILTER(ALLSELECTED('Table'),'Table'[Month]<=MAX('Table'[Month]))))By the way, you could set visual level filter is not blank
Result:
and here is sample pbix file, please try it.
Regards,
Lin
3 Replies
v-lili6-msftCommunity Support
hi Kolumam
For your case, you must use a spreated Month tables as the slicer
Dim Month = VALUES('Table'[Month])Then use this measure in your visual.
Measure = IF(MAX('Table'[Month])<=MAX('Dim Month'[Month]),CALCULATE(SUM('Table'[Size]),FILTER(ALLSELECTED('Table'),'Table'[Month]<=MAX('Table'[Month]))))By the way, you could set visual level filter is not blank
Result:
and here is sample pbix file, please try it.
Regards,
Lin
- Ashish_Mathur
Super User
Hi,
You may refer to my solution here - Flex a Pivot Table to show data for x months ended a certain user defined month.
Hope this helps.
- chawalit
Helper I
In this situation, I have date table and Sales table to ralated with date column. So, the DAX will be like this
Cumulative Qty =
IF (
MIN ( 'Date'[Date] ) <= CALCULATE ( MAX ( Sales[Order Date] ), ALL ( Sales ) ),
CALCULATE (
SUM ( Sales[Quantity] ),
FILTER ( ALL ( 'Date'[Date] ), 'Date'[Date] <= MAX ( 'Date'[Date] ) )
)Hope this help.
)
