Forum Discussion
To have blank value for future date in Line/table chart
I would like to build a Visual Sales by day for current 3 months. Normally, 1st few days of the month sales is 0, thus i create a measure to have 0 value for blank value in order the line chart will start at day 1 every month. But as a result day beyond also display as 0.
The problem that i am facing now is i do not wan the 0 value beyond todays date which make the chart weird sudden drop for day beyond that.
I tried with this formula also doesn't works.
Hi thew
Since you're using the day of month, you will need to identify today's month and day of month and incorporate them in a condition
MTD Sales = VAR _MonthNumberToday = MONTH ( TODAY () ) VAR _DayOfMonthToday = DAY ( TODAY () ) VAR _MTD = CALCULATE ( [Sales] + 0, DATESMTD ( Dates[Date] ) ) VAR _LatestMonth = IF ( MONTH ( MAX ( Dates[Date] ) ) = _MonthNumberToday, IF ( SELECTEDVALUE ( Dates[Day of Month] ) <= _DayOfMonthToday, _MTD ) ) RETURN COALESCE ( _MTD, _LatestMonth )Please see the attached sample pbix.
3 Replies
- rajendraongole1
Super User
Hi thew - modify your measure as below:
MTD Sales with Zero =
VAR CurrentDate = MAX('Calendar'[Date])
VAR Today = TODAY()
RETURN
IF(
CurrentDate > Today,
BLANK(), // Ensures future dates do not appear on the chart
COALESCE([MTD Sales], 0) // Replaces blank values with 0 only for past and current dates
)I hope this helps
- danextian
Hi thew
Since you're using the day of month, you will need to identify today's month and day of month and incorporate them in a condition
MTD Sales = VAR _MonthNumberToday = MONTH ( TODAY () ) VAR _DayOfMonthToday = DAY ( TODAY () ) VAR _MTD = CALCULATE ( [Sales] + 0, DATESMTD ( Dates[Date] ) ) VAR _LatestMonth = IF ( MONTH ( MAX ( Dates[Date] ) ) = _MonthNumberToday, IF ( SELECTEDVALUE ( Dates[Day of Month] ) <= _DayOfMonthToday, _MTD ) ) RETURN COALESCE ( _MTD, _LatestMonth )Please see the attached sample pbix.
Thanks alot, its works!! 😀
