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.

danextian's avatar
danextian
Super User
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.

  • thew's avatar
    thew
    Frequent Visitor
    1 year ago

    Thanks alot, its works!! ðŸ˜€