cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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

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.

6 REPLIES 6
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

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

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.

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.

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.

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.

Go to My LinkedIn Page

Frequent Visitor

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] ))RETURNIF (    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!

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!

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

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors