Show items with no data -- Date range limitation issue

[ITManuel]

Hi all,

I know there are several threads about this topic out there, but so far I couldn't get to the right answer.

I come accross this issue several times and even I was partially able to solve it, I would like to get to the bottom of the issue and really understand it. In order to do this I have created a sample PBIx file in which I have:

• A Date table ranging from 01.01.2023 to 31.12.2025
• A Fact table with values for some days between 01.01.2024 and 08.05.25

My goal is to show the sum of values in a bar chart by a Year | Months hierarchy on the axis while showing also months with no values in between 01.01.2024 and 08.05.25 but not showing anything before Janury 2024 nor after May 2025

This is my data model:

These are the results of my attempts:

Visual 1.0:

Has the [Date] column of the date table on the X-axis and the [Value] column on the Y-axis. Empty dates between the minimum date with values and the maximum date with values are shown, however empty dates prior to the minimum date and after to maximum date are hidden by default. This would actually be the desired behaviour. --> Without the option "Show items with no data", values are shown in between, but not outside the minimum or maximum date with values.

Visual 1.1

Is the same as 1.0, with the option "Show items with no data" for X-axis activated. 

Visual 2.0

Has a Year | Months hierarchy on the X-axis and the [Value] column on the Y-axis. The behaviour is now different than in visual 1.0, months with empty values are not shown anymore. I do not understand why on the day granularity the visual behaves different than on the months granularity.

Visual 2.1

Is the same as 2.0, with the option "Show items with no data" for X-axis activated. 

Visual 3.0

Is the same as 2.0 with a filter on the visual itself where 'Date'[Date] is filtered accordingly. --> This would be the desired result

Visual 4.0

Uses the [Measure 1] trying to limit the date range shown on the X-axis

Measure1 = 
VAR _MinDate =
    CALCULATE ( MIN ( Fact[Date] ), ALL ( 'Fact'[Date] ) )
VAR _MaxDate =
    CALCULATE ( MAX ( Fact[Date] ), ALL ( 'Fact'[Date] ) )
VAR _Result =
    CALCULATE (
        SUM ( Fact[Value] ),
        'Date'[Date] >= _MinDate,
        'Date'[Date] <= _MaxDate
    )
RETURN
    _Result

Visual 4.1

Uses the [Measure 2] trying to limit the date range shown on the X-axis

Measure2 = 
VAR _MinDate =
    CALCULATE ( MIN ( Fact[Date] ), ALL ( 'Fact'[Date] ) )
VAR _MaxDate =
    CALCULATE ( MAX ( Fact[Date] ), ALL ( 'Fact'[Date] ) )
VAR _Result =
    CALCULATE (
        SUM ( Fact[Value] ),
        FILTER ( ALL ( 'Date' ), 'Date'[Date] >= _MinDate && 'Date'[Date] <= _MaxDate )
    )
RETURN
    _Result

Visual 4.2

Uses the [Measure 3] trying to limit the date range shown on the X-axis

Measure3 = 
VAR _MinDate =
    CALCULATE ( MIN ( Fact[Date] ), ALL ( 'Fact' ) )
VAR _MaxDate =
    CALCULATE ( MAX ( Fact[Date] ), ALL ( 'Fact' ) )
VAR _Result =
    SUM ( Fact[Value] )
VAR _ResultLimited =
    IF (
        AND ( MAX ( 'Date'[Date] ) >= _MinDate, MAX ( 'Date'[Date] ) <= _MaxDate ),
        _Result
    )
RETURN
    _ResultLimited

Visual 4.3

Uses the [Measure 4] trying to limit the date range shown on the X-axis

Measure4 = 
VAR _MinDate =
    CALCULATE ( MIN ( Fact[Date] ), ALL ( 'Fact' ) )
VAR _MaxDate =
    CALCULATE ( MAX ( Fact[Date] ), ALL ( 'Fact' ) )
VAR _T1 =
    FILTER (
        VALUES ( 'Date'[Date] ),
        'Date'[Date] >= _MinDate
            && 'Date'[Date] <= _MaxDate
    )
VAR _T2 =
    ADDCOLUMNS ( _T1, "@Value", CALCULATE ( SUM ( 'Fact'[Value] ) ) )
VAR _Result =
    SUMX ( _T2, [@Value] )
RETURN
    _Result

Visual 4.4

Uses the 'Fact[Value] column or whatever measure with the [LimitDateRange]=1 filter on the visual.

LimitDateRange = 
VAR _MinDate =
    CALCULATE ( MIN ( Fact[Date] ), ALL ( 'Fact' ) )
VAR _MaxDate =
    CALCULATE ( MAX ( Fact[Date] ), ALL ( 'Fact' ) )
VAR _Result =
    IF (
        AND ( MIN ( 'Date'[Date] ) >= _MinDate, MIN ( 'Date'[Date] ) <= _MaxDate ),
        1,
        0
    )
RETURN
    _Result

Non of the attempts to limit the date range on the X-axis with the calculating measure worked, only the visuals 3.0 and 4.4 provide the desired result using filter on the visual level. 

I have read in several posts here in the forum that limiting the calculation for a certain date range in the calculcating measure should work, but non of measures ( 1 -  4 ) which should replicate such suggestion works in my case, only visual level filtering does.

The PBIx is provided via the following link:

https://we.tl/t-PjlyVOVv76

Any help / explanation is much appreciated.

Thanks in advance

Best regards

[LinnCh]

Hello, 

the solution in visual 4.0 seem to be what I need to solve my issue, however I am very new to PBI and I cannot understand how to use the formula in my own data.

Background: I have a dataset with continues dates and multiple articles. I want to show the sales on these articles with the option of filtering down on individual articles. When filtering some dates do not have data and then the x-axis do not show the dates. I would like to see the whole timeline although no data. 

Could you please help me? 

[Daniel29195 · Accepted solution]

@ITManuel 

create a calculated column im dimdate table , as follow : 
01.01.2024 and 08.05.25

switch( true() , 

dimdate[date] >="2024-01-01" && dimdate[date] <="2025-08-25" , 1, 0 )

then set this calculated column on the filter pane, filter on page,   and set it  = 1 . 

let me know if this works for you .

If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠

[ITManuel]

Hi @Daniel29195 ,

thank you for your answer. 

Of course your proposed solution will work, so is my solution for visual 3.0 and 4.4. I rather prefer to create a dediacated measure and use it on the visual filter as for visual 4.4, than creating an additional column in the 'Date' table as I would need to add multiple columns since the solution would be required for multiple visuals in the same report.