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

     

AshokKunwar's avatar
AshokKunwar
Continued Contributor
7 months ago

Hii rk1904 

The reason your current measure fails with the "Show items with no data" setting is that that setting overrides your KEEPFILTERS logic at the visual level. You need a second measure to act as a "gatekeeper" for the X-axis.

​Step 1: Create the Filter Flag Measure

​Create this new measure to determine if a month belongs in the visual:

Filter_Last6Months = 
VAR DateActual = MAX('Table Dates'[Date])
VAR PreviousDates = DATESINPERIOD('Table Dates'[Date], DateActual, -6, MONTH)
VAR IsInPeriod = 
    COUNTROWS(
        INTERSECT(
            VALUES('Table Dates Aux'[Date]), 
            PreviousDates
        )
    )
RETURN
    IF(IsInPeriod > 0, 1, 0)

 

Step 2: Apply the Visual-Level Filter

  1. ​Select your line chart visual.
  2. ​Open the Filters Pane.
  3. ​Drag the new Filter_Last6Months measure into the "Filters on this visual" section.
  4. ​Set the filter to "is 1" and click Apply filter.

​Step 3: Refine your original measure

​To ensure the line chart stays continuous even with zeros, slightly adjust your Nº App measure to return a 0 instead of a BLANK when it is within the period:

Nº App = 
VAR DateActual = MAX('Table Dates'[Date])
VAR PreviousDates = DATESINPERIOD('Table Dates'[Date], DateActual, -6, MONTH)
VAR Result =
    CALCULATE (
        COUNT(report_credit[AppNumber]),
        REMOVEFILTERS ('Table Dates'),
        KEEPFILTERS ( PreviousDates ),
        USERELATIONSHIP ( 'Table Dates Aux'[Date], 'Table Dates'[Date] )
    )
-- Force a 0 if we are in the last 6 months but the count is blank
RETURN
    IF( [Filter_Last6Months] = 1, COALESCE(Result, 0), BLANK() )

 

If this solves your X-axis range issue, please mark this as the "Accepted Solution" to help others!