Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to Solution.
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] )
)
)
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?
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] )
)
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
23 | |
20 | |
15 | |
13 |
User | Count |
---|---|
51 | |
42 | |
39 | |
19 | |
19 |