Count dates between two dates within the same column

[Yggdrasill]

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
B	1.3.2019	Dog
B	1.6.2019	Snake
C	1.1.2020	Snake
C	2.1.2020	Dog
C	3.1.2020	Snake
B	1.1.2020	Cat

I want to count the number of Categories I have in each dimension for every date I choose to filter so that the output is like this for today's date

Dimension	Date	Count of Category
Dog	22.4.2020	1
Cat	22.4.2020	1
Snake	22.4.2020	1

But when I select another date, for example 1.st of February 2020 the result would be this

Dimension	Date	Count of Category
Dog	1.2.2020	0
Cat	1.2.2020	2
Snake	1.2.2020	1

So basically I need to count the rows in my Calendar table that fall between two dates corresponding to the Category and Dimension columns.

How can I achieve this usind DAX ?

What I've tried so far is using FILTER(), and ERLIER() but my DAX skills are not up to the task I'm afraid.

[v-alq-msft]

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]

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:

Case	Date	Status from	Status to
A	1.1.2020	New	Open
A	20.1.2020	Open	Closed
B	1.1.2020	New	Open
B	13.1.2020	Open	Pending
B	1.2.2020	Pending	Closed

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]

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]

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 · Accepted solution]

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.

[mitsu]

Hi You could just use the category as value on your visual and select its aggregation as count . Alternatively create a new measure as

Count(Table1[Category]) .

Hope this helps !

[Yggdrasill]

That won't work as I need to count all the dates between the two dates. Your suggestion will only count for the dates registered in the transaction table - Hence, I need to count the dates in the Calendar table in order to have the value 1 for each Category that falls between the two dates.

You can look at this model as a demo for a Ticket system where the status of the ticket for company A, B, C... can have the value Open, Pending, In Order and Closed where each status has it's own date.

I need to be able to count all Pending orders from any date I choose to view at any given time and even filter it by company A, B, C etc