Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
GerardoRTobar
Helper I
Helper I

YTD Measure to be blank if no data for date

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!

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

View solution in original post

2 REPLIES 2
GerardoRTobar
Helper I
Helper I

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
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors