cancel
Showing results 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

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

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.

1 ACCEPTED SOLUTION
Super User

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com
9 REPLIES 9
Super User

Hi,

Regards,
Ashish Mathur
http://www.ashishmathur.com
Resolver I

Hi Ashish,

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

Super User

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Resolver I

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.

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?

Super User

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

Regards,
Ashish Mathur
http://www.ashishmathur.com
Resolver I

Hi Ashish,

First I apologise for confusing the issue.

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

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
Super User

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

Regards,
Ashish Mathur
http://www.ashishmathur.com
Super User

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

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

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

Resolver I

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.

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

Best wishes for the New Year 2023!

DAXRichard

Announcements

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

#### Power BI Monthly Update - July 2024

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

#### Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors