Skip to main content
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Resolver I
Resolver I

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


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).


  • 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.






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.


Ashish Mathur

View solution in original post

Super User
Super User


Share the download link of the PBi file.

Ashish Mathur

Hi Ashish,

See download link below.


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.


Ashish Mathur

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  IFISBLANK ( [Pax] ), BLANK (),
      • This solves for showing the 12th month (returns blank). This is what I want to do.


  • 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?




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]))))

Ashish Mathur

Hi Ashish,


First I apologise for confusing the issue.

Your question:

  • Does this measure work?


  • 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 (),
            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.

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

Ashish Mathur
Super User
Super User

@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:

Power BI — Year on Year with or Without Time Intelligence



Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD:


Power BI Offset Compare Categories, Time Intelligence MOM, QOQ, and YOY:




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!


Helpful resources

Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.