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