cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Resolver I
Resolver I

Running Total

Hi,

I need to calculate running totals over hierarchical period i.e quarters,months,weeks. I have tried using STARTOFYEAR() and FIRSTDATE() but couldn't get the solution. I need to put it into the line chart where the axis is hierarchical. I guess, period context is affecting STARTOFYEAR and FIRSTDATE.

Thank you!

4 REPLIES 4
Super User IV
Super User IV

@ziyabikram96 , these are various way to have running totals with ate table

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date])) //monthly

QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date]))) //qrtly

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31")) //yearly

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=maxx(date,date[date]))) //overall
Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=max(Sales[Sales Date])))

 

Can you share sample data and sample output in table format?

 

 

 

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.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

I have a Sales Table & a Calendar Table where Sales table has many-to-1 relation with Calendar table. Here is my measure:

Sales from first day =
VAR firstdatee = CALCULATE(FIRSTDATE(SALES[Date]),ALL('CALENDAR'[Quarter Description]),ALL('CALENDAR'[Month Short]),ALL('CALENDAR'[Week in Year]))
VAR lastdatee = LASTDATE(SALES[Date])
RETURN
CALCULATE(SUM(SALES[Sales]),DATESBETWEEN(SALES[Date],firstdatee,lastdatee))
Community Support
Community Support

Hi, @ziyabikram96 

 

Running total calculation depends on the period used to display the result. I created data to reproduce your scenario to calculate the running total 3 month. The pbix file is attached in the end.

Table:

d1.png

 

Calendar(a calculated table):

Calendar = CALENDARAUTO()

 

There is a relationship between two tables. You may create a measure as below.

Running total 3 month = 
IF(
    NOT(ISBLANK(SELECTEDVALUE('Table'[Date]))),
    CALCULATE(
        SUM('Table'[Value]),
        DATESINPERIOD(
            'Calendar'[Date],
            LASTDATE('Calendar'[Date]),
            -3,MONTH
        )
    )
)

 

Result:

d2.png

 

Best Regards

Allan

 

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

But, it has to be dynamic. Like, I don't have to specifically mention "-3" using DATESINPERIOD function. It should take just first and last date and then calculate running total between that period.

Helpful resources

Announcements
November Update

Check it Out!

Click here to read more about the November 2020 Updates!

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform October Community Highlights

Check out the top community contributors across all of the communities

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors