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.

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 ?

 

 

 

 

v-alq-msft's avatar
v-alq-msft
Community Support
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.