Forum Discussion

thew's avatar
thew
Frequent Visitor
1 year ago
Solved

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 ...
Need Help
  • danextian's avatar
    danextian
    1 year ago

    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.

rajendraongole1's avatar
rajendraongole1
Super User
1 year ago

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