Forum Discussion

rk1904's avatar
rk1904
New Member
7 months ago
Solved

Use show items with no data with dynamic x-axis dates

Hello, I have a measure that depending on the date chosen in the slicer, will display the last 6 months up to that selected date. However, if the measure has no value for any of those months, the m...
Need Help
  • cengizhanarslan's avatar
    cengizhanarslan
    7 months ago

     

    1. Keep Year-Month from Table Dates Aux on the X-axis

    2. Turn on Show items with no data for that axis field

    3. Add this measure and filter the visual to Flag = 1

    Axis - Last 6 Months Flag =
VAR AnchorDate =
    MAX ( 'Table Dates'[Date] )          -- date selected in slicer (Table Dates)
VAR EndMonth =
    EOMONTH ( AnchorDate, 0 )
VAR ThisMonth =
    EOMONTH ( MAX ( 'Table Dates Aux'[Date] ), 0 )
RETURN
IF (
    ThisMonth <= EndMonth
        && ThisMonth > EOMONTH ( EndMonth, -6 ),
    1,
    0
)

    Then:

    • Put Axis - Last 6 Months Flag into the Visual level filters

    • Set it to is 1

    • Keep Show items with no data = On

     

danextian's avatar
danextian
Super User
7 months ago

hi rk1904 

Use a disconnected dates table for the date selection and create this measure:

Nº App =
VAR DateActual =
    CALCULATE (
        MAX ( 'DisconnectedDates'[Date] ),
        ALLSELECTED ( DisconnectedDates )
    )
VAR MinDate =
    EDATE ( DateActual, -6 )
VAR CurrentDate =
    MAX ( 'Table Dates'[Date] )
RETURN
    IF (
        CurrentDate > MinDate && CurrentDate <= DateActual,
        SUM ( 'Data'[Value] ) + 0
    )

The flat lines in the gif below are dates with no data.

Please see the attached pbix.