Forum Discussion
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 month doesn't appear on the x-axis. So I tried use the option "show items with no data," but with this option all the months that exist in the table (Table Dates) appear, not just the last 6 months.
Can someone help me with this question?
Measure:
Keep Year-Month from Table Dates Aux on the X-axis
Turn on Show items with no data for that axis field
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
4 Replies
Keep Year-Month from Table Dates Aux on the X-axis
Turn on Show items with no data for that axis field
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
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
- Select your line chart visual.
- Open the Filters Pane.
- Drag the new Filter_Last6Months measure into the "Filters on this visual" section.
- 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!
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.
HII rk1904
If this solves your X-axis range issue, please mark this as the "Accepted Solution" to help others!
