Forum Discussion

Yggdrasill's avatar
Yggdrasill
Responsive Resident
6 years ago
Solved

Count dates between two dates within the same column

My table looks like this and in my model has a 1:N relationship with my Calendar Table from Date to Date: Category Date Dimension A 1.1.2020 Cat A 3.2.2020 Dog B 1.1.2019 Cat ...
Need Help
Show and Tell
  • v-alq-msft's avatar
    v-alq-msft
    6 years ago

    Hi, Yggdrasill 

     

    You may modify 'var _date = SELECTEDVALUE('Calendar'[Date])' as 'var _date = MAX('Calendar'[Date])' and make other codes unchanged.

     

    CountValue = 
var _date = MAX('Calendar'[Date])
var _status = SELECTEDVALUE(Test[Status])
var tab = 
SUMMARIZE(
    ALLSELECTED('Table'),
    'Table'[Case],
    "MaxDate",
    var c = [Case]
    return
    CALCULATE(
        MAX('Table'[Date]),
        FILTER(
            ALLSELECTED('Table'),
            'Table'[Case] =c&&
            'Table'[Date]<=_date
        )
    )
)
var newtab = 
ADDCOLUMNS(
    tab,
    "Status",
    var c = [Case]
    var md = [MaxDate]
    return
    MAXX(
        FILTER(
            ALLSELECTED('Table'),
            'Table'[Case] = c&&
            'Table'[Date] = md
        ),
        [Status to]
    )
)
var result = 
COUNTROWS(
        FILTER(
            newtab,
            [Status] = _status
        )
    )
return
IF(
    ISBLANK(result),
    0,
    result
)

     

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

v-alq-msft's avatar
v-alq-msft
Community Support
6 years ago

Hi, Yggdrasill 

 

I wonder if there is something wrong with the expected result on 2/1/2020. I assume that you want to calculated the number of distinct categories where the date is less than or equal to the selected date for each dimension.

 

Table:

 

Calendar:

 

Calendar = CALENDARAUTO()

 

 

There is no relationship between two tables.

 

You may create a measure as below.

 

Count = 
var _date = SELECTEDVALUE('Calendar'[Date])
var _dimension = SELECTEDVALUE('Table'[Dimension])
return
CALCULATE(
    DISTINCTCOUNT('Table'[Category]),
    FILTER(
        ALL('Table'),
        'Table'[Dimension] = _dimension&&
        'Table'[Date]<=_date
    )
)

 

 

Result:

 

Best Regards

Allan

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

  • Yggdrasill's avatar
    Yggdrasill
    Responsive Resident
    6 years ago

    Thanks for taking the time Allan but this doesn't seem to return the desired output.

    For 1.st of February the active cases should be 2 Cats and 1 snake. (please note I'm using dd.mm.yyyy format)

    Furthermore the measure doesn't seem to work.

     

    My table is actually like this:

     

    CaseDateStatus fromStatus to
    A1.1.2020NewOpen
    A20.1.2020Open

    Closed

    B1.1.2020New

    Open

    B13.1.2020OpenPending
    B1.2.2020PendingClosed


    I need to count the dates between each status changes for each and every Case and be able to visualize it like so for all cases


    • v-alq-msft's avatar
      v-alq-msft
      Community Support
      6 years ago

      Hi, Yggdrasill 

       

      Based on your data, I created data to reproduce your scenario.
      Table:

       

      Test:

       

      Calendar:

       

      Calendar = CALENDARAUTO()

       

       

      You may create a measure as below.

       

      CountValue = 
var _date = SELECTEDVALUE('Calendar'[Date])
var _status = SELECTEDVALUE(Test[Status])
var tab = 
SUMMARIZE(
    ALLSELECTED('Table'),
    'Table'[Case],
    "MaxDate",
    var c = [Case]
    return
    CALCULATE(
        MAX('Table'[Date]),
        FILTER(
            ALLSELECTED('Table'),
            'Table'[Case] =c&&
            'Table'[Date]<=_date
        )
    )
)
var newtab = 
ADDCOLUMNS(
    tab,
    "Status",
    var c = [Case]
    var md = [MaxDate]
    return
    MAXX(
        FILTER(
            ALLSELECTED('Table'),
            'Table'[Case] = c&&
            'Table'[Date] = md
        ),
        [Status to]
    )
)
var result = 
COUNTROWS(
        FILTER(
            newtab,
            [Status] = _status
        )
    )
return
IF(
    ISBLANK(result),
    0,
    result
)

       

       

      Result:

       

      Best Regards

      Allan

       

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

       

      • Yggdrasill's avatar
        Yggdrasill
        Responsive Resident
        6 years ago

        Thank you Allan ! This is something I can use ! Brilliant !

         

        I want to note though that the calculations are slow because I have more than 2000 Cases and a span of more than 5 years.

         

        Also, the count breaks when I use date hierachy in the visuals.

         

        Is it possible to use the end of each date as a breaking point when I want to up the date granularity ? 

         

        For example if I use a barchart with Years, I would see the final count for each year ?