topic Re: Restrict YTD with Max order date and keeps filter of Calender table in DAX Commands and Tips

Restrict YTD with Max order date and keeps filter of Calender table

Rahul_SC — Mon, 30 Jan 2023 18:56:29 GMT

Hi All,

how can I bring previous row value to the next row. Like in below table, i do not have value in 13th Jan, so it should show 12th Jan Value.

I have using the below dax because I have to restric ytd upto the max order date.

Total YTD (current) =

TOTALYTD([Total sales],

'Calender Table'[Date],

'Calender Table'[Date] <= MAX(Data[Order Date])

)

Thanks.

Re: Restrict YTD with Max order date and keeps filter of Calender table

SivaMani — Mon, 30 Jan 2023 22:38:29 GMT

Rahul_SC , Try to replace the blank in the [Total sales] measure.

https://learn.microsoft.com/en-us/dax/coalesce-function-dax

Re: Restrict YTD with Max order date and keeps filter of Calender table

Rahul_SC — Tue, 31 Jan 2023 06:48:38 GMT

Hi SivaMani ,

Thanks for responding.

I replaced blank with 0 in total sales measure. but It is not giving what I want. Instead of 0, it should show previous value which is available like YTD.

And it is also showing the data for full year. If you see the below image, I do not have sales data after 27-Jan, there also it is showing 0 now. it should be blank as sale is not done after this date.

One solution worked if I restrict my calender table using this. But I do not want to restrict calender table because it is used in defferent visuals where I need all the dates for full year.

CALENDAR(MIN(Data[Order Date]),MAX(Data[Order Date]))

thanks

Re: Restrict YTD with Max order date and keeps filter of Calender table

wdx223_Daniel — Tue, 31 Jan 2023 07:13:55 GMT

Total YTD (current) =

TOTALYTD([Total sales],

'Calender Table'[Date],

'Calender Table'[Date] <= MAX('Calender Table'[Date])

)

Re: Restrict YTD with Max order date and keeps filter of Calender table

Rahul_SC — Tue, 31 Jan 2023 07:21:29 GMT

wdx223_Daniel ,

I am already using this dax (see in my post). It is showing blank where order is not placed on any date.

I am instead of blank it should show previous YTD value.

Re: Restrict YTD with Max order date and keeps filter of Calender table

wdx223_Daniel — Tue, 31 Jan 2023 07:35:29 GMT

in the Function of MAX, use the date column from Calendar Table, instead of Data Table

Re: Restrict YTD with Max order date and keeps filter of Calender table

Rahul_SC — Tue, 31 Jan 2023 07:44:53 GMT

Hi,

Did this.

Total YTD (current) =

TOTALYTD([Total sales],

'Calender Table'[Date],

'Calender Table'[Date] <= MAX('Calender Table'[Date])

)

It is repeating the value on the day where order did not happen. Now, it show blank after 27-Jan as this is the last order date.

Re: Restrict YTD with Max order date and keeps filter of Calender table

MarkLaf — Mon, 06 Feb 2023 22:50:11 GMT

It sounds like you want the current results but blanks after your latest sales order date? This may be what you want:

Total YTD (current) = VAR _currentDate = MAX( 'Calender Table'[Date] ) VAR _maxOrderDate = CALCULATE( MAX( Data[Order Date] ), REMOVEFILTERS( Data ) ) RETURN IF( _currentDate <= _maxOrderDate, TOTALYTD( [Total sales], 'Calender Table'[Date] )+0 )

Here is the output with 'Show items with no data' turned on so you can see the measure dropping off after 27 Jan 2018:

Note that if you want the measure to be blank instead of zero before the first order, then remove the "+0" after the TOTALYTD function.