Measure overlapping date periods from table and date slicer

[Anonymous]

Hello everybody,

I am quite new to Power BI and try to solve a task, that I think should be rather easy, however I cannot fiure it out.

In my tabel are two columns like start_date and end_date. Now I want to apply something like a date slicer to filter the data, preferably slicing monthly periods.

What I want to get is the number of rows in my table, that are "active" during the slicer-period, that is, if the period from start_date to end_date is overlapping with the current month from the date slicer (it might also contain the whole month).

Does someone know how to solve this?

I would very much appreciate any help!

[v-xicai · Accepted solution]

Hi @Anonymous ,

You may create calendar table without creating relationship with your data table, then add the date of calendar table to the slicer visual to filter data.

Calendar= CALENDARAUTO()

Then you can create measure like DAX below.

CountRows=

Var SlicerDate=SELECTEDVALUE(Calendar[Date])

Return

CALCULATE(COUNTROWS('Table'),FILTER(ALLSELECTED('Table'),'Table'[start_date ]<=SlicerDate&&'Table'[end_date]>=SlicerDate))

Best Regards,

Amy 

Community Support Team _ Amy

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

[Anonymous]

Hi @v-xicai 

that is exactly what I was looking for! Thank you very much!

[ibarrau]

Hey, assuming you have a column that will tell you if the row is "active" then you just need to create the measure. Check this example:

https://docs.microsoft.com/en-us/power-bi/desktop-tutorial-create-measures#create-a-measure

Regards,

[ibarrau]

Hi. First let's go with slicers. If you have two columns "From - To" you can add the StartDate as a individual slicer type "After...". Then add other individual filter with EndDate as type "Before...". There you have your range date filtering with dates.

If you want the count of the rows you need to build a DAX Measure like this:

MeasureCount =
CALCULATE (
    COUNTROWS(Table),
    Table[Column] = "Active"
)

This new measure field will only show the rows with Active in that column. The value of the dates will change with the slicers filtering this measure too.

Hope this helps,

[Anonymous]

Hi @ibarrau , thanks for your reply!

I still don't understand how to obtain this Column, with "Active" entries. Is there a way to create a column in dependent on the slicer date?