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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. 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.


Go to My LinkedIn Page


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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.