Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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.
Solved! Go to Solution.
Whoops. I turns out the problem was something else entirely, unrelated to the TOTALYTD calculations. Nevermind!
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
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.
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.
I tried this, too, but got the same result:
SumGiftsFYTD = TOTALYTD([SumGifts], DATESYTD('Calendar'[Date], "6/30"), ALL('Calendar'[Date]), "6/30")
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
111 | |
108 | |
108 | |
93 | |
61 |
User | Count |
---|---|
169 | |
138 | |
135 | |
102 | |
86 |