YTD Measure to be blank if no data for date

[GerardoRTobar]

Hello everyone.

As the YTD is an aggregated measure, when there's no data for a date, it still returns a value in the YTD measure and I don't want this to happen. For example: if on January 4th I sold 45k units, I have no data for Jan-5th and on January 6th I sold 10k units, the output for the common YTD would be: Jan-4th: 45k, Jan-5th: 45k and Jan-6th: 55k. I don't want that Jan-5th data point for the YTD as there's no actual data for that date. It is messing up my report.

I appreciate your kind help!

[GerardoRTobar]

Just to add another thing to my inquiry:

I'm actually using a DAX workaround for not projecting the YTD to the future in the Actual Year, as it actually does.

So my DAX code for the YTD measure actually goes like this:

Sales YTD =

VAR LastSalesDate = MAXX(ALL(Sales[Purchase Date]), Sales[Purchase Date])
VAR YTDSales = CALCULATE (SUM(Sales[Value]), DATESYTD (Dates[Date])

RETURN
IF(MIN(Dates[Date]) <= LastSalesDate, YTDSales, BLANK())

Would an "AND ISNOTBLANK(Sales[Value])" clause in the IF be enough?

[Anonymous · Accepted solution]

Hi @GerardoRTobar 

Leaving a gap between days when calculating YTD is not the best idea since it really is true that YTD for such a day is the same amount as for the previous day's YTD. If you really don't want to show the YTD number for a period of time (not only a day but a week, month...) that did not have any transactions/sales, then you'd do this:

[Total Sales] = SUM( Sales[Value] )


[Sales YTD] =
IF( NOT ISEMPTY( Sales ),
    CALCULATE(
        [Total Sales],
        DATESYTD( Dates[Date] )
    )
)