topic Re: DAX command to get total sales for YTD in Direct mode in DAX Commands and Tips

DAX command to get total sales for YTD in Direct mode

atresadern — Thu, 27 Apr 2023 10:05:08 GMT

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

Re: DAX command to get total sales for YTD in Direct mode

johnt75 — Thu, 27 Apr 2023 11:14:24 GMT

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" ) )

Re: DAX command to get total sales for YTD in Direct mode

atresadern — Thu, 27 Apr 2023 11:23:06 GMT

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" )

)

Re: DAX command to get total sales for YTD in Direct mode

johnt75 — Thu, 27 Apr 2023 11:28:16 GMT

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.

Re: DAX command to get total sales for YTD in Direct mode

atresadern — Thu, 27 Apr 2023 11:48:54 GMT

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

Re: DAX command to get total sales for YTD in Direct mode

johnt75 — Thu, 27 Apr 2023 12:52:49 GMT

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.

Re: DAX command to get total sales for YTD in Direct mode

atresadern — Thu, 27 Apr 2023 13:27:01 GMT

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).