Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Yggdrasill
Responsive Resident
Responsive Resident

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:

CategoryDateDimension
A1.1.2020Cat
A3.2.2020Dog
B1.1.2019Cat
B1.3.2019Dog
B1.6.2019Snake
C1.1.2020Snake
C2.1.2020Dog
C3.1.2020Snake
B1.1.2020Cat


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

DimensionDateCount of Category
Dog22.4.20201
Cat22.4.20201
Snake22.4.20201

 

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

DimensionDateCount of Category
Dog1.2.20200
Cat1.2.20202
Snake1.2.20201


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.

1 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.

View solution in original post

7 REPLIES 7
v-alq-msft
Community Support
Community Support

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:

c1.png

 

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:

c2.png

 

Best Regards

Allan

 

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

 

 

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


cases.PNG

Hi, @Yggdrasill 

 

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

h1.png

 

Test:

h2.png

 

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:

h5.png

 

Best Regards

Allan

 

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

 

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 ?

 

 

 

 

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
Resolver IV
Resolver IV

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
Responsive Resident
Responsive Resident

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.