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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
DAXRichArd
Resolver I
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.

Thanks in advance for all your help.

 

DAXRichArd_0-1671588177591.png

 

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

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

9 REPLIES 9
Ashish_Mathur
Super User
Super User

Hi,

Share the download link of the PBi file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi Ashish,

See download link below.

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

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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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?

DAXRichArd_0-1672938333748.pngDAXRichArd_1-1672938392356.png

 

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
https://www.linkedin.com/in/excelenthusiasts/

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

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
https://www.linkedin.com/in/excelenthusiasts/
amitchandak
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: 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

 

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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