Forum Discussion

ITManuel's avatar
ITManuel
Responsive Resident
2 years ago

Show items with no data -- Limiting date range issue

Hi,

 

I have a column chart which simply show values which have been sumed up by a measure.

 

 

 

 

 

 

 

Activating the "Show items with no data" option the chart turns into the following, visualizing the entire date range ( + blank row ) of the date table on the x-Axis, which I guess is a normal behaviour. 

 

 

 

 

 

 

 

 

 

I would like to keep the  "Show items with no data" option in order to visualize items with no data in between the first appearance and the last appearance of data but would like to exclude on the x-Axis anything before the first and after the last date with date, so exclude anything before September 2023 and after November 2025 in the above case. 

 

I tried to limit the calculation with the following code, but unfortunately it does not work:

 

 

Inv = 
VAR _MinDate =
    CALCULATE (
        MIN ( Tasks[TaskStartDate] ),
        Tasks[TaskIsSummary] = FALSE,
        Tasks[_Invoicingamount] > 0
    )
VAR _MaxDate =
    CALCULATE (
        MAX ( Tasks[TaskStartDate] ),
        Tasks[TaskIsSummary] = FALSE,
        Tasks[_Invoicingamount] > 0
    )
VAR _Result =
    CALCULATE (
        SUM ( Tasks[_Invoicingamount] ),
        Tasks[TaskIsSummary] = FALSE,
        FILTER ( ALL ( 'Date' ), 'Date'[Date] >= _MinDate && 'Date'[Date] <= _MaxDate )
    )
RETURN
    _Result

 

 

 

The _MinDate and _MaxDate are retreiving the Min- and MaxDate correctly, but the resctriction of the date x-Axis is not working at all. 

 

This is my datamodel:

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Thanks in advance and best regards

Need Help

4 Replies

  • nirali_arora's avatar
    nirali_arora
    Resolver II
    2 years ago

    Instead of filter, you should try if statement like the following example

    0 between range 
    Measure = var _1= SUM(Opportunity[Opportunity count]) +0 
    var _min = minx(ALLSELECTED('Calendar'), 'Calendar'[Date]) 
    var _max = maxx(ALLSELECTED('Calendar'), 'Calendar'[Date]) 
    return
    if(max('Calendar'[Date]) <_min || max('Calendar'[Date]) >_max , BLANK(), _1)

    • ITManuel's avatar
      ITManuel
      Responsive Resident
      2 years ago

      Hi nirali_arora ,

       

      I tried your proposed solution but unfortunately it is not working. I would assume that both solutions work, but none of it does in my case. 

       

       

       

       

       

       

       

       

       

       

      InvTest = 
VAR _MinDate =
    CALCULATE (
        MIN ( Tasks[TaskStartDate] ),
        Tasks[TaskIsSummary] = FALSE,
        Tasks[_Invoicingamount] > 0
    )
VAR _MaxDate =
    CALCULATE (
        MAX ( Tasks[TaskStartDate] ),
        Tasks[TaskIsSummary] = FALSE,
        Tasks[_Invoicingamount] > 0
    )
VAR _MinDate1 =
    MINX ( ALLSELECTED ( 'Date' ), 'Date'[Date] )
VAR _MaxDate1 =
    MAXX ( ALLSELECTED ( 'Date' ), 'Date'[Date] )
VAR _Result =
    CALCULATE ( SUM ( Tasks[_Invoicingamount] ), Tasks[TaskIsSummary] = FALSE ) + 0
VAR _ResultLimited =
    IF (
        MAX ( 'Date'[Date] ) > _MinDate1
            || MAX ( 'Date'[Date] ) < _MaxDate1,
        _Result,
        BLANK ()
    )
RETURN
    _ResultLimited

       

       

       

      • ITManuel's avatar
        ITManuel
        Responsive Resident
        2 years ago

        Hi,

         

        I still have the same issue described above which I was unable to resolve. When I activate the "Show items with no data" option on the Date X-Axis, the entire range of the Date table is shown, even if in the measure the calculation should be limited to a date range between Min and Max of available data. 

         

        This is my data: 

         

         

         

         

        This is my model:

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

        This is the measure I'm using:

         

        InvPWA = --Invoiced according to 100% Milestone in PWA
VAR _MinDate =
    CALCULATE (
        MIN ( Tasks[TaskStartDate] ),
        TasksHeader[TaskIsMilestone] = 1,
        Tasks[_Invoicingamount] > 0,
        Tasks[TaskPercentCompleted] = 100
    )
VAR _MaxDate =
    CALCULATE (
        MAX ( Tasks[TaskStartDate] ),
        TasksHeader[TaskIsMilestone] = 1,
        Tasks[_Invoicingamount] > 0,
        Tasks[TaskPercentCompleted] = 100
    )
VAR _Result =
    CALCULATE (
        SUM ( Tasks[_Invoicingamount] ),
        TasksHeader[TaskIsMilestone] = 1,
        Tasks[_Invoicingamount] > 0,
        Tasks[TaskPercentCompleted] = 100,
        'Date'[Date] >= _MinDate
            && 'Date'[Date] <= _MaxDate
    )
VAR _ResultLimited =
    IF (
        AND ( MAX ( 'Date'[Date] ) >= _MinDate, MAX ( 'Date'[Date] ) <= _MaxDate ),
        _Result
    )
RETURN
    _Result

         

         

        _MinData return correctly the 09.01.2023 as the first date with available data and _MaxDate return correctly the 30.06.2023 as the last date with available data.

        However the result in the bar chart is the following, always showing the entire date range of the date table on the X-Axis, limiting the calculation to the desired range in between _MinDate and _MaxDate is not working.

        Neither the _Result nor _ResultLimited variable works.

         

         

        Thanks in advance