Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I need the running total to start over for every new months, example:
Date | Sales | cumulative total | |
01-01-2020 | 10 | 10 | |
02-01-2020 | 20 | 30 | |
03-01-2020 | 10 | 40 | |
04-01-2020 | 5 | 45 | |
05-01-2020 | 10 | 55 | |
29-01-2020 | 5 | 60 | |
30-01-2020 | 15 | 75 | |
31-01-2020 | 5 | 80 | |
01-02-2020 | 10 | 10 | |
02-02-2020 | 15 | 25 | |
03-02-2020 | 5 | 30 |
Running total= CALCULATE ( SUM ( Table1[Reserve Beginning Balance] ), FILTER ( ALL ( Table1 ), Table1[Date] <= MAX ( Table1[Date] ) ) )
Solved! Go to Solution.
Hi @aagj ,
At first, you need to create a new column to get "YYYY-MM".
YM =
FORMAT('Table'[Date],"yyyymm")
Then you could use column or measure to get the running total.
Column:
Column =
CALCULATE (
SUM ( 'Table'[Sales] ),
FILTER (
'Table',
'Table'[YM] = EARLIER ( 'Table'[YM] )
&& 'Table'[Date] <= EARLIER ( 'Table'[Date] )
)
)
Measure:
Measure =
CALCULATE (
SUM ( 'Table'[Sales] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[YM] = MAX ( 'Table'[YM] )
&& 'Table'[Date] <= MAX ( 'Table'[Date] )
)
)
Here is my test file for your reference.
Hi @aagj ,
At first, you need to create a new column to get "YYYY-MM".
YM =
FORMAT('Table'[Date],"yyyymm")
Then you could use column or measure to get the running total.
Column:
Column =
CALCULATE (
SUM ( 'Table'[Sales] ),
FILTER (
'Table',
'Table'[YM] = EARLIER ( 'Table'[YM] )
&& 'Table'[Date] <= EARLIER ( 'Table'[Date] )
)
)
Measure:
Measure =
CALCULATE (
SUM ( 'Table'[Sales] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[YM] = MAX ( 'Table'[YM] )
&& 'Table'[Date] <= MAX ( 'Table'[Date] )
)
)
Here is my test file for your reference.
@aagj , Use MTD with date calendar
MTD Sales = CALCULATE(SUM(Table1[Sales]),DATESMTD('Date'[Date]))
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...
Appreciate your Kudos.
User | Count |
---|---|
140 | |
113 | |
104 | |
76 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |