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:

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.

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.

7 Replies

  • mitsu's avatar
    mitsu
    Resolver IV
    6 years ago

    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's avatar
      Yggdrasill
      Responsive Resident
      6 years ago

      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

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