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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
Stewwe
Helper I
Helper I

Year to Date marker in a date table

Hello everybody,

I have the following problem:

I would like to be able to mark the following values "YTD" in my date table:

1.1.19-19.11.19
1.1.18-19.11.18
1.1.17-19.11.17
etc.

I am very grateful for any idea!

Thank you

Steffen

1 ACCEPTED SOLUTION

Hi @Stewwe ,

You can create the following calculated column in your Date table:

YTD_Today =
VAR monthtoday =
    MONTH ( TODAY () ) * 100
        + DAY ( TODAY () )
VAR monthday =
    MONTH ( 'Calendar'[Date] ) * 100
        + DAY ( 'Calendar'[Date] )
RETURN
    monthtoday > monthday

Then apply this column to the filter and select "True":

11.PNG

Here is a demo, please try it:

https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/ETrs1jCJgbZBs57Bqn... 

Best Regards,

Community Support Team _ Joey
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

4 REPLIES 4
v-joesh-msft
Solution Sage
Solution Sage

Hi @Stewwe ,

Use DATESYTD or TOTALYTD to change the parameter "year_end_date" to "11/19", the default is December 31.

1.PNG

Best Regards,

Community Support Team _ Joey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hello @v-joesh-msft ,

if I want to solve this by measures, your approach is perfectly clear to me.

But what I want is to include the YTD logic in my date dimension in order to be able to filter through various measures.

According to my logic, a calculated column must mark all date entries up to today's date.

I hope my question became a little clearer.

Bye

Steffen

Hi @Stewwe ,

You can create the following calculated column in your Date table:

YTD_Today =
VAR monthtoday =
    MONTH ( TODAY () ) * 100
        + DAY ( TODAY () )
VAR monthday =
    MONTH ( 'Calendar'[Date] ) * 100
        + DAY ( 'Calendar'[Date] )
RETURN
    monthtoday > monthday

Then apply this column to the filter and select "True":

11.PNG

Here is a demo, please try it:

https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/ETrs1jCJgbZBs57Bqn... 

Best Regards,

Community Support Team _ Joey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thank you very much @v-joesh-msft . I am completely thrilled by the simplicity of the formula.

Helpful resources

Announcements
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! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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