Forum Discussion

Rvn86's avatar
Rvn86
Helper I
9 years ago
Solved

Count date previous date rows based on slicer selection

Hi,

 

I want to count the number of date rows based on a date chosen from a slicer. 

 

I have the following columns:

 

Startdate

End date

 

I want to count every date with a start date if the end date is smaller than the chosen date. 

 

Example: 

 

 

With the example above, I want for example to choose the date "01.02.2015", which should give me 3 as the result from the count. 

 

Any ideas? I have tried several DAX formulas, but cannot get it working with slicers. 

 

Thank you :) 

  • v-huizhn-msft's avatar
    v-huizhn-msft
    9 years ago

    Hi Rvn86,

    You can try the following steps to check.

    1. Create a new table including the slicer field, and get the selected value in new table. More details, please review this thread. 

    2. Create a card visual to display your measure value, so you can debug it step by step.

    Best Regards,
    Angelia

7 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    9 years ago

    I might be missing something but I do not understand how that chosen date results in a count of 3. The only earlier date would be the 2014 end date and all of the other end dates are later and total a count of 4...

    • Rvn86's avatar
      Rvn86
      Helper I
      9 years ago

      I was poorly explained from me, but in the example above the first row has an end date in 2014. The last row has a start date in 2016, so by that you will get the total count of 3. 

       

      So there are two variables to consider: If the start date is at a date below the chosen date, and if the end date is at a later date than the chosen date. 

       

      Sorry for the bad explanation in the first post, and thank you for your reply. 

       

      Any tips? :) 

      • v-huizhn-msft's avatar
        v-huizhn-msft
        Microsoft Employee
        9 years ago

        Hi Rvn86,

        First, please create a measure to get the value selected in slicer.

        selectedDate = MAXX(ALLSELECTED('Date'),'Date'[Date])


        Second, please create a measure to get expected result.

        result = CALCULATE(COUNTROWS(Example),FILTER(Example,Example[End date]>='Date'[selectedDate]&&Example[Start date]<='Date'[selectedDate]))



        Please feel free if you have any question.

        Best Regards,
        Angelia