Forum Discussion

ziyabikram96's avatar
ziyabikram96
Helper V
5 years ago

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

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    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-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


    Appreciate your Kudos.

    • ziyabikram96's avatar
      ziyabikram96
      Helper V
      5 years ago

      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))
  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    5 years ago

    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:

     

    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:

     

    Best Regards

    Allan

     

     

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

    • ziyabikram96's avatar
      ziyabikram96
      Helper V
      5 years ago

      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.