Setting DATESYTD to current year and no filter impact on it

[Dunner2020]

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?

[Icey]

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 · Accepted 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.

[Jihwan_Kim]

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

[billyb]

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]

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:

• https://community.powerbi.com/t5/Desktop/Setting-the-Default-Value-of-a-Slicer/m-p/16442 
• https://www.fourmoo.com/2018/09/11/power-bi-default-slicer-selection/

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!

[selimovd]

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

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic