Filter Data between Start Date and End Date in Power BI

Question

Hey newbie here to DAX:

I have project data with ProjectName, StartDate, EndDate and UsagePercentage.

I want to display usage percentage by time e.g. how much was the UsagePercentage in Quarter 1 or just a single month.

If I currently use slicers based on Start and End date it does not show me the correct result.

In the example above If I choose January as my month my Usage should be the one highlighted in yellow since the start date is before January and end date is in January.

I believe I have to create a date slicer like start date <= Date <= End date for this to work. But I am not sure how to create this and apply it to the report. Any help?

This shouldn't be hard with two slicers. To do it with just one, you'll need to write measures with filtering. — Alexis Olson
I have slicers for: year, quarter, month....i need to do measure with filtering. I am writing a DAX but it is returning me incorrect information — hkhan
This is a bit peculiar tho. Supposing you chose 'Q1 2019' -- what would you expect the result to be? My interpretation is that the range in the first row covers some but not all of Q1 so how would you expect that 10 percent to be represented in the result? — Ryan B.
I am trying to figure out the same..If i choose 'Q1-2019' It should show me the results from Jan/Feb/March such that: Jan--> records with start date less than Jan and end date greater than Jan. Feb--> records with start date less than Feb but End date greater than Jan. This result should exclude results with End date in January. — hkhan

Alexis Olson Alexis Olson · Accepted Answer · 2019-04-26T20:49:38

You can write a measure where you take the max and min of the values selected by your slicer:

Percentage = 
VAR StartDate = MIN ( Slicer[Date] )
VAR EndDate = MAX ( Slicer[Date] )
RETURN
    CALCULATE (
        SUM ( DataTable[PercentageOfUse] ),
        DataTable[StartDate] <= StartDate,
        DataTable[EndDate] >= EndDate
    )

Your particular situation might be different, but this may point you in the right direction.

Filter Data between Start Date and End Date in Power BI

1 Answers