From experience trying to figure out running totals in Power BI, they can be extremely tricky. It is very easy to end up with negative values, or a running total that resets more often than you would want. In this tutorial we will look at how you can control you running totals in PBI to obtain the visuals wanted!
The first step would be to create a data table, this will facilitate everything! It will permit using every date of the year. This is required as PBI does not recognise any date that is originally not in the data, meaning if you are to do date calculations for example non existant dates will not appear. For this purpose we can use CalendarAUTO(). Creating a new table with just this function will essentially act as a calendar, inputting all the dates between the start and end of your data.
Next you can right click on your date table and select 'mark as date table'
Using the format() function you can then create new columns equivalent to the part of the date you require. Here is an example:
Date Table = ADDCOLUMNS( CALENDARAUTO(), "Weekday", FORMAT([Date],"dddd") ,"Weekday Number", WEEKDAY([Date],2) , "Month Year", FORMAT([Date], "MMM, YYYY") , "Month Year Sory", FORMAT([Date], "YYYYMM") , "Year Quarter", FORMAT([Date], "YYYY, \Quarter Q")
)
Now we can finally create our running total, based on one of those columns using this code:
Overall running total = CALCULATE(SUM(Orders[Sales]) , REMOVEFILTERS() , _date[Date] <= MAX(_date[Date]))
This calculates the running total of sales! You can base it on a specific category by writing in the calculation function:
Category[Category_field] = "Value"
You can do this with different measures and fields as well as have it repeat. If you want to restart the running total every year you can use this in your calculate function:
_date[Year]=Max(_date[Year])
Adding it will restart for each year. It corresponds in tableau to using a table calculation and unticking the 'year' box.
I hope this helps! It takes quite a few step but the date table is essential to many PowerBI functions you might want to use in the future!