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

Get 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

Reply
Dunner2020
Post Prodigy
Post Prodigy

Setting DATESYTD to current year and no filter impact on it

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?

1 ACCEPTED SOLUTION
Icey
Community Support
Community Support

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_ )

YTD.gif

 

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.

View solution in original post

6 REPLIES 6
Icey
Community Support
Community Support

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

Icey
Community Support
Community Support

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_ )

YTD.gif

 

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.

Jihwan_Kim
Super User
Super User

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.

 

Picture2.png

 

Picture3.png

 

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.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

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!

Sumanth_23
Memorable Member
Memorable Member

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!

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!

Proud to be a Super User!



selimovd
Super User
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" ) )

 

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
 
Best regards
Denis
 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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