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

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

      • Rvn86's avatar
        Rvn86
        Helper I
        9 years ago

        Hi v-huizhn-msft

         

        Thank you very much. 

         

        I have made your measures, but the only thing I cannot get working is to get the "result" to react with slicer selections. 

         

        My slicer is from a separate date table with a relationship to the table with the data. Anything I am missing?

         

        Thank you:)

         

        //Rvn