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

[Rahul_SC]

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.

[wdx223_Daniel]

Total YTD (current) =

       TOTALYTD([Total sales],

                'Calender Table'[Date],

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

                )

[Rahul_SC]

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

[wdx223_Daniel]

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

[Rahul_SC]

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.

[MarkLaf]

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.

[Rahul_SC]

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

[SivaMani]

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

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