Count date previous date rows based on slicer selection

[Rvn86]

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 🙂 

[Greg_Deckler]

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]

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]

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]

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

[v-huizhn-msft]

Hi @Rvn86,

For my example, the slicer is also from a separate table. Please check if slicer effect your report by clicking "Edit interactions" under Fomant on home page. Please see the following screenshot, you should select the funnel button rether than the circle button.



Best Regards,
Angelia

[Rvn86]

Thanks again, @v-huizhn-msft 🙂

Yes, I know about this option, but it is not the interaction itself. When I choose a date, the formula does not consider the chosen date. It only counts when I choose days with data in it. 

I think it has to do with the date table and the relation between my data and the date table. 

I will work on this later this week, and follow up with more questions if I do not solve it.

Thank you very much for your willingness to reply. I really appreciate it. 

[v-huizhn-msft · Accepted solution]

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