Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hi there,
I am calculating the YTD sum for a regulatory year using the following measure
YTD sum = calculate([sum_measure], DATESYTD(Dates[Date],"31/3"))
I have got data of the last 4 regulatory years. When I select the current regulatory year from the dropdown list of regulatory year filters then the above-written filter works. However, when there is no selection of regulatory year then the above measure does not work, it returns blank. Is there any way that I could set up the above-mentioned measure to always the current regulatory year and there should not be any impact of the regulatory year filter on it?
Solved! Go to Solution.
Hi @Jihwan_Kim ,
I agree with @Jihwan_Kim. Please check if your scenario is like what he described.
In addition, the expression of this fiscal year YTD can be modified like so:
VAR FirstDayofThisFY =
IF (
MONTH ( TODAY () ) <= 3,
DATE ( YEAR ( TODAY () ) - 1, 4, 1 ),
DATE ( YEAR ( TODAY () ), 4, 1 )
)
VAR ThisFYYTD_ =
CALCULATE (
[sum_measure],
DATESBETWEEN ( Dates[Date], FirstDayofThisFY, TODAY () )
)
Then, the measure could be created like so:
YTD sum =
VAR YTD_ =
CALCULATE ( [sum_measure], DATESYTD ( Dates[Date], "31/3" ) )
VAR FirstDayofThisFY =
IF (
MONTH ( TODAY () ) <= 3,
DATE ( YEAR ( TODAY () ) - 1, 4, 1 ),
DATE ( YEAR ( TODAY () ), 4, 1 )
)
VAR ThisFYYTD_ =
CALCULATE (
[sum_measure],
DATESBETWEEN ( Dates[Date], FirstDayofThisFY, TODAY () )
)
RETURN
IF ( ISFILTERED ( Dates[Fiscal Year] ), YTD_, ThisFYYTD_ )
For more details, please check the attched .pbix file.
If this is not what you want, please give us more details.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Dunner2020 ,
Can the replies above solve your problem?
If they can, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.
If not, could you share us more details?
Best Regards,
Icey
Hi @Jihwan_Kim ,
I agree with @Jihwan_Kim. Please check if your scenario is like what he described.
In addition, the expression of this fiscal year YTD can be modified like so:
VAR FirstDayofThisFY =
IF (
MONTH ( TODAY () ) <= 3,
DATE ( YEAR ( TODAY () ) - 1, 4, 1 ),
DATE ( YEAR ( TODAY () ), 4, 1 )
)
VAR ThisFYYTD_ =
CALCULATE (
[sum_measure],
DATESBETWEEN ( Dates[Date], FirstDayofThisFY, TODAY () )
)
Then, the measure could be created like so:
YTD sum =
VAR YTD_ =
CALCULATE ( [sum_measure], DATESYTD ( Dates[Date], "31/3" ) )
VAR FirstDayofThisFY =
IF (
MONTH ( TODAY () ) <= 3,
DATE ( YEAR ( TODAY () ) - 1, 4, 1 ),
DATE ( YEAR ( TODAY () ), 4, 1 )
)
VAR ThisFYYTD_ =
CALCULATE (
[sum_measure],
DATESBETWEEN ( Dates[Date], FirstDayofThisFY, TODAY () )
)
RETURN
IF ( ISFILTERED ( Dates[Fiscal Year] ), YTD_, ThisFYYTD_ )
For more details, please check the attched .pbix file.
If this is not what you want, please give us more details.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @Dunner2020
I am not quite sure whether I correctly followed your question in order to create a sample pbix file that looks similar to your case, but please check the two pictures down below.
If the year is not selected, then the result of your measure will be always last year in your dim-date-table.
Like the first picture below. (last year is 2021).
However, I assume you have something like until the year 2026 in your dim-date table.
Like the second picture below.
In this case, try something like the below, or you can change the below a bit based on your preference.
Sales YTD =
IF (
ISFILTERED ( Dates[Year] ),
CALCULATE ( [Sales Total], DATESYTD ( Dates[Date], "31/3" ) ),
CALCULATE (
[Sales Total],
DATESBETWEEN ( Dates[Date], DATE ( 2021, 4, 1 ), TODAY () )
)
)
Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
Linkedin: linkedin.com/in/jihwankim1975/
Twitter: twitter.com/Jihwan_JHKIM
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi @Jihwan_Kim, thanks!
Your answer pointed me to the right direction, I was able to achieve a similar need like @Dunner2020 using the below DAX:
YTD Revenue (default to current year) =
var Revenue =
CALCULATE (
SUM ( fact[Revenue] ),
USERELATIONSHIP ( fact[Revenue posting period], '1 - Date'[Date] )
)
RETURN
IF (
ISFILTERED ( '1 - Date'[Year] ),
CALCULATE ( [Revenue], DATESYTD ( '1 - Date'[Date] ) ),
CALCULATE (
[Revenue],
DATESBETWEEN ( '1 - Date'[Date], DATE ( YEAR ( TODAY () ), 1, 1 ), TODAY () )
)
)
This serves my need, thanks!
hi @Dunner2020 - Currently there is no out of the box setting to update slicers to update automatically to for e.g. Current month / Current year. You can implement an approach as mentioned in the below posts:
This would require to make changes to your data model to calculate the Current month / Current year.
Do vote/mark this post as a solution and provide kudos 👍 if it helped you with your question. This would also help others on the community who may have similar questions. Thanks!
Proud to be a Super User!
Hey @leo_89 ,
depending on your local settings the date could be false. Try maybe to change the end of year date to "3/31".
What always works is to use a regular date, the year will be ignored anyways.
So try the following:
YTD sum = CALCULATE( [Measure], DATESYTD( Dates[Date], "2020-03-31" ) )
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
90 | |
84 | |
70 | |
49 |
User | Count |
---|---|
143 | |
121 | |
112 | |
59 | |
58 |