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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

TOTALYTD() summing over calendar year instead of using custom <year_end_date> for fiscal year

I'm using Power BI Desktop Version: 2.40.4554.463 64-bit (October, 2016)

 

I'm working on a report that compares donations/gifts by fiscal year YoY. I created a date table using

Calendar = CALENDARAUTO()

and connected the date value in the gift facts table to the date value in the Calendar table in the Modeling view.

 

Next, I created a simple SumGifts measure using this DAX:

 

SumGifts = SUM(All_Gifts_Since_2010[Gf_Amount])

 

 

Then, I created a measure for the fiscal year-to-date (FYTD) sum of donations using

SumGiftsFYTD = TOTALYTD([SumGifts], 'Calendar'[Date], "6/30")

I added the custom year_end_date to TOTALYTD as our fiscal year ends on June 30. However, the value generated for SumGiftsFYTD is the sum of gifts from January 1, 2016, to November 5, 2016, rather than from July 1, 2016, to November 5, 2016, as expected.

 

I tried a different method for calculating SumGiftsFYTD using the CALCULATE() function like this:

 

SumGiftsFYTD2 = CALCULATE(
   [SumGifts],
    DATESYTD('Calendar'[Date], "06-30"),
    ALL('Calendar'[Date])
)

However, I still get the same result. It's summing for the calendar year rather than the fiscal year.

 

My ultimate goal here is to use this in a area chart with SumGifts as the value, FiscalPeriod as the X axis, and FiscalYear as the legend, where FiscalPeriod is the fiscal month, i.e. the FiscalPeriod for  July = 1.

 

Anybody have any ideas as to what is causing this? I've tried every suggestion I can find on the internet. I suspect it's something simple.

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Whoops. I turns out the problem was something else entirely, unrelated to the TOTALYTD calculations. Nevermind!

View solution in original post

5 REPLIES 5
Anonymous
Not applicable

Whoops. I turns out the problem was something else entirely, unrelated to the TOTALYTD calculations. Nevermind!

Would you mind sharing the issue that you had? I am trying to do the same thing and am having the same issues.

 

Thank you!

Emma

Anonymous
Not applicable

I wish I could tell you, but I don't remember. I've regretting not sharing the issue in this post. I'm not sure why I didn't, but I think it was embarrasment is how silly the problem was.

The only thing that comes to mind is this: at one point on that project I had an issue where my calendar table extended out several years into the future. That caused some problem that was fixed by having the calenader table end on Dec 31 of the year of the last date in the fact table.

So, if the last date in the fact table was May 17, 2017, my calendar table needed to end on Dec 31, 2017.

I don't think that was the issue that fixed the problem discussed here, but it could have been. Worth a shot to check on that I guess.

Also, I learned much of what I needed for this project from Johann's blog at databear.com. Here are a few choice posts:

http://databear.com/2016/05/08/power-bi-tips-calculating-year-to-date-values/
http://databear.com/2016/11/08/power-bi-tip-dynamic-calendar-table/
http://databear.com/2016/05/26/power-bi-tips-calculate-one-of-the-most-used-dax-functions/

Note that if you copy and paste code from those posts, you'll have to straighten the single quote marks and replace en dashes with hyphens. Wordpress added smart quotes and replaced hyphens with en dashes when publishing.

Anonymous
Not applicable

I wish I could tell you, but I don't remember. I've regretting not sharing the issue in this post. I'm not sure why I didn't, but I think it was embarrasment is how silly the problem was.

 

The only thing that comes to mind is this: at one point on that project I had an issue where my calendar table extended out several years into the future. That caused some problem that was fixed by having the calenader table end on Dec 31 of the year of the last date in the fact table.

 

So, if the last date in the fact table was May 17, 2017, my calendar table needed to end on Dec 31, 2017.

 

I don't think that was the issue that fixed the problem discussed here, but it could have been. Worth a shot to check on that I guess.

 

Also, I learned much of what I needed for this project from Johann's blog at databear.com. Here are a few choice posts:

 

http://databear.com/2016/05/08/power-bi-tips-calculating-year-to-date-values/

http://databear.com/2016/11/08/power-bi-tip-dynamic-calendar-table/

http://databear.com/2016/05/26/power-bi-tips-calculate-one-of-the-most-used-dax-functions/

 

Note that if you copy and paste code from those posts, you'll have to straighten the single quote marks and replace en dashes with hyphens. Wordpress added smart quotes and replaced hyphens with en dashes when publishing.

Anonymous
Not applicable

I tried this, too, but got the same result:

SumGiftsFYTD = TOTALYTD([SumGifts], DATESYTD('Calendar'[Date], "6/30"), ALL('Calendar'[Date]), "6/30")

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.