CODEWITHSUNDEEP
reportpowerbipowerbi-desktop
hkhan
hkhan

Reputation: 863

Filter Data between Start Date and End Date in Power BI

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. enter image description here

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?

Upvotes: 1

Views: 5994

Answers (1)

Alexis Olson
Alexis Olson

Reputation: 40254

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.

Upvotes: 1

Related Questions