Running monthly total for a specific year (2019). Cumulative total / running total.

[DAXRichArd]

Hello,

I work with aviation data.

• I want to create a running total for a specific year.
• That year is 2019.
• The 2019 running total will be used as a baseline in my analysis.

I used TOTALYTD for the current year.

• I added a filter SAMEPERIODLASTYEAR to get the previous year's running total.
• I used DATEADD to get earlier years including year 2019 (I subtracted 3 intervals of YEAR from 2022 to arrive at 2019).

Problem.

• In calendar year 2023, my DATEADD minus 3 intervals will return year 2020 data.
• Is there a way to get a running total for a specific year?
• I want the year to be locked at year 2019.

Thanks in advance for all your help.

[Ashish_Mathur]

Hi,

Share the download link of the PBi file.

[DAXRichArd]

Hi Ashish,

See download link below.

https://myaccount.dropsend.com/file/89f406b612de5c4b

[Ashish_Mathur · Accepted solution]

Hi,

This measure works

Measure = CALCULATE([Pax],DATESBETWEEN(_Calendar[Date],date(2019,1,1),date(2019,MONTH(max(_Calendar[Date])),day(MAX(_Calendar[Date])))))

Hope this helps.

[DAXRichArd]

Thanks Ashish.

I experimented with DATESBETWEEN. It shows that you know what you're doing and I don't.  🙂

I have one more question.

Current Situation

• My most current data set has 11 months of data for calendar year 2022.
  • My data set has complete calendar years for previous months.
• I wish to display months and totals for 11 months for current year and previous years.
  • I begin my calculation with  IF ( ISBLANK ( [Pax] ), BLANK (),
    • This solves for showing the 12th month (returns blank). This is what I want to do.

Problem

• Eventhough I only display 11 months for the current year, the previous year totals are calcualted for the 12 months.
• How can I have the previous years sum only for the months available in the current year?

[Ashish_Mathur]

Hi,

Does this measure work?

Measure = CALCULATE([Pax],DATESBETWEEN(_Calendar[Date],date(2019,1,1),date(2019,MONTH(maxx(ALL(_Calendar),_Calendar[Date])),day(MAX(_Calendar[Date]))))

[DAXRichArd]

Hi Ashish,

First I apologise for confusing the issue.

Your question:

• Does this measure work?

Answer:

• Yes! Yes it does! Thank you!

I have an observation where, if you see the screen shots from yesterday (05Jan2023), although my outer filter displays 11 months of the current year (in this case year 2022), the previous time series sum 12 months instead of 11.

I modified your code by adding an IF statment:

Pax YTD 2019 =

IF (

    ISBLANK ( [Pax] ),

    BLANK (),

    CALCULATE (

        [Pax],

        DATESBETWEEN (

            _Calendar[Date],

            DATE ( 2019, 1, 1 ),

            DATE ( 2019, MONTH ( MAX ( _Calendar[Date] ) ), DAY ( MAX ( _Calendar[Date] ) ) )

        )

    )

)

Sorry for the confusion.

I can create a new post to address this issue.

Once again, thx. Your solution worked and I greately appreciate it.

DAXRichard

[Ashish_Mathur]

You are welcome.  i do not understand your requirement at all.  Have you tried the formula i posted yesterday?

[amitchandak]

@DAXRichArd ,

You can get like

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))

2nd last year =CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))

3rd  last year =CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-3,Year),"12/31"))

if you select all years in the visual then ytd should work for each year (the first formula)

Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA

Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc

Power BI Offset Compare Categories, Time Intelligence MOM, QOQ, and YOY: https://youtu.be/5YMlkDNGr0U

[DAXRichArd]

Hello,

I appreciate the responses.

The YTD solution I recieved is similar to what I've done already.

In calendar year 2022, minus 3 years will give me year 2019 (2019 - 3 = 2019).

However, when the year changes to 2023, minus 3 gives me 2020.

The solution I hoped to arrive at is to have code and locks the result to year 2019. It's a maintenance issue. I wish to avoid having to remember to update the code every calendar year.

Your thoughts are welcomed.

And thanks to everyone for your responses. I truely do appreciate your responses.

Best wishes for the New Year 2023!

DAXRichard