Date column in DATESYTD

[Yoojin]

Hi all,

I have a question about why I must use a date column from a date table, not a data table, for DATESYTD.

I do know I should use a date column in a date table for the argument but I don't think I fully understand how it works..

I have created the two tables below for comparison. The first one has a Date column from the Calendar table as the argument for DATESYTD and it works the way it should. The second one however does not retreive YTD sales amount.

I would like to know why the InvoiceDate in the Sales table is not working the same way. I expected it would make no difference because the InvoiceDate column contains dates too and the DATESYTD could do the math with having all the valid dates.

DATESYTD with the date column in the Calendar date table

[Total Net Sales YTD] = CALCULATE([Total Net Sales], DATESYTD(Calendar[Date]))

Month	Total Net Sales	Total Net Sales YTD
Jan	$1,250,000	$1,250,000
Feb	$1,000,000	$2,250,000
Mar	$1,500,000	$3,750,000
Apr	$2,000,000	$5,750,000
May	$1,700,000	$7,450,000
Jun	$1,800,000	$9,250,000
Jul	$2,000,000	$11,250,000
Grand Total	$11,250,000	$11,250,000

DATESYTD with the date column in the Sales data table

[Total Net Sales YTD] = CALCULATE([Total Net Sales], DATESYTD(Sales[InvoiceDate]))

Month	Total Net Sales	Total Net Sales YTD
Jan	$1,250,000	$1,250,000
Feb	$1,000,000	$1,000,000
Mar	$1,500,000	$1,500,000
Apr	$2,000,000	$2,000,000
May	$1,700,000	$1,700,000
Jun	$1,800,000	$1,800,000
Jul	$2,000,000	$2,000,000
Grand Total	$11,250,000	$11,250,000

Could anyone help me understand the reason why the reference to dates in a data table instead of a date/calendar table is not working? Thank you and excuse my ignorance.

[v-binbinyu-msft · Accepted solution]

Hi @Yoojin ,

DATESYTD() is a time-intelligence function, If you want these functions to work properly, a date table that holds consecutive dates is a prerequisite for it.

For more details, you can read related document: DATESYTD – DAX Guide

Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[VIJAYKUMART]

You need create 2 relationships with Calender Table

1.  calender date to sales date - active relationship - represents with line 

2. calender date to Invoice date  - inactive relationship - represents with dotted line (---)

write DAX like below 

[Total Net Sales YTD] = CALCULATE([Total Net Sales], USERELATIONSHIP(CALENDER(DATE), SALES(INVOICEDATE)),DATESYTD(Sales[InvoiceDate]))

[Yoojin]

Thanks VIJAYKUMART. I already created a relationship between the Invoice Date in the Sales table and the Date column in the Calendar table. 🙂 Otherwise how could I get the first result using DATESYTD? What I wanted to know was, why DATESYTD wasn't working with the Invoice Date column in the data table (Sales) even though it contains dates too. Given the comments the other friends left, it seems to have something to do with consecutiviness of dates...? Anyway, thanks for your help and letting me know the USERELATIONSHIP DAX!

[Ashish_Mathur]

Hi.

I think there are 2 valid reasons for working with a Calendar Table rather than the invoice date column of your Data Table:

• The Calendar Table has consecutive dates including public holidays and weekends.  The invoice date column may not have consecutive dates
• By creating a Calendar Table, you can create relationships from multiple tables to it 

[speedramps]

Only Power BI novices use in build date functions

It is best pratice to use a calendar table  with  year and month offsets

see https://www.youtube.com/watch?v=BtYn1hfdSAM

and https://www.youtube.com/watch?v=5RB3Jdfrn1s

do all these training modules and yoy will never go back to using DATESYTD again

A calendar table with offsets it a much better way to calculate MTD, YTD, PM and PY

Thanks for reaching out for help.

I put in a lot of effort to help you, now please quickly help me by giving kudos.

Remeber we are unpaid volunteers and here to coach you with Power BI and DAX skills and techniques, not do the users job for them. So please click the thumbs up and accept as solution button. 

If you give someone a fish then you only give them one meal, but if you teach them how to fish then they can feed themselves and teach others for a lifetime.  I prefer to teach members on this forum techniques rather give full solutions and do their job. You can then adapt the technique for your solution, learn some DAX skills for next time and soon become a Power BI Super User like me. 

One question per ticket please. If you need to extend your request then please raise a new ticket.

You will get a quicker response and each volunteer solver will get the kudos they deserve. Thank you !