DAX command to get total sales for YTD in Direct mode

[atresadern]

Hi 

I have a table with SalesAmount and SalesDate and want to be able to see the YTD total.  Our year runs from 1st May-30April.

This is the code I have but I keep getting syntax incorrect.

YTD Billing = CALCULATE(SUM(vw_PBI_Dashboard_Bills[BilledAmount]), DATESYTD(MIN(DATE(YEAR(vw_PBI_Dashboard_Bills[CreatedDate]), MONTH(vw_PBI_Dashboard_Bills[CreatedDate]), VALUE(TEXT(RIGHT(vw_PBI_Dashboard_Bills[CreatedDate], 2))))), "4/30"))

Thanks

Alison

[atresadern]

Thanks for your response, I appreciate your input.  I can get it working with a different calendar table which is limited to just the dates in our year but not the full date table which has 20 years of dates (I could probably reduce this).  

[atresadern]

I have a date table created from an article but it's not working when I use 

DATESYTD ( 'Date'[Date], "4/30" )

rather than the Created date.  I've linked the table and tried linking both ways but got blank results - any ideas?

Thanks

[johnt75]

There needs to be some element from the date table in the visual. If there isn't, try adding a filter so that the date is in this day.

[johnt75 · Accepted solution]

For the time intelligence functions to work correctly you need a proper date table, marked as a date table. Link the 'Date'[Date] column to your vw_PBI_Dashboard_Bills[CreatedDate] column and then the measure becomes simply

YTD Billing =
CALCULATE (
    SUM ( vw_PBI_Dashboard_Bills[BilledAmount] ),
    DATESYTD ( 'Date'[Date], "4/30" )
)

[atresadern]

Thanks, @johnt75 

This seems to work, rather than linking the date table, any reason why I shouldn't do it this way?

YTD Billing =

CALCULATE (

    SUM ( vw_PBI_Dashboard_Bills[BilledAmount] ),

    DATESYTD ('vw_PBI_Dashboard_Bills'[CreatedDate], "4/30" )

)

[johnt75]

That might work but it is considered best practice to have a proper date table. As well as guaranteeing that the time intelligence functions work as expected you can break the data down by month, week etc and you can add other useful columns such as is the day a working day or not. There's lots of articles and videos on creating a date table.