CODEWITHSUNDEEP
powerbidaxpowerquerypowerpivotmeasure
MalyMajo
MalyMajo

Reputation: 75

Date filtering of DAX measure in pivot chart

I have created 3 DAX measures in Power Pivot to calculate backlog value and I have an issue with filtering in pivot chart. When I filter out a specific time period, the backlog calculates only based on filtered dates. Instead of filtering, I would like to only "zoom in" to some specific time-range.

Measures

Opened:=CALCULATE(COUNTA([CreatedOn]))
Closed:=CALCULATE(COUNTA(Ticket[ClosedOn]),USERELATIONSHIP(Ticket[ClosedOn],'Calendar'[Date]))
Backlog:=CALCULATE([Opened]-[Closed],FILTER(ALLSELECTED('Calendar'),'Calendar'[Date]<=MAX('Calendar'[Date])))

Based on example data below, without filtering any dates the backlog value is following:

Date;Backlog
1/1/2021;1
2/1/2021;3
3/1/2021;1
4/1/2021;3

When I filter dates excluding 1st of January, I get following values:

Date;Backlog
2/1/2021;2
3/1/2021;1
4/1/2021;3

I want to get same values as without filtering the date. I just want to to "zoom in" on some specific date range. In my case i have 1 year of input data and want to see the backlog evolution of last 8 weeks. So when I filter last 8 weeks, my first week shows only value calculated for that week, but instead it should take into account all the values from previous periods + the result of first week

Example data

TicketNumber;Type;CreatedOn;ClosedOn;Status
ticket1;Service Request;1/1/2021;1/3/2021;Closed
ticket2;Incident Record;1/2/2021;1/3/2021;Closed
ticket3;Incident Record;1/2/2021;1/5/2021;Closed
ticket4;Service Request;1/4/2021;;Open
ticket5;Service Request;1/4/2021;;Waiting for

Upvotes: 0

Views: 257

Answers (1)

MalyMajo
MalyMajo

Reputation: 75

Fixed thanks to my colleague! :)

I had to change ALLSELECTED to ALL

Backlog:=CALCULATE([Opened]-[Closed],FILTER(ALL('Calendar'),'Calendar'[Date]<=MAX('Calendar'[Date])))

Upvotes: 0

Related Questions