Forum Discussion
DAX Measure
Anonymous so as I understand, Measure1 is working as intended but Measure2 is not with the revised dataset.
Try this out
Measure2 = VAR _0 = CALCULATE ( MAX ( Project[EndPeriod] ), FILTER ( VALUES ( Project[Organization] ), Project[Organization] = CALCULATE ( MAX ( Tickets[Organization] ) ) ) ) VAR _1 = CALCULATE ( COUNT ( Tickets[Case] ), FILTER ( Tickets, Tickets[StartPeriod] > _0 && Tickets[StartPeriod] <= _0 + 30 ) ) RETURN _1
Anonymous
Measure1 =
CALCULATE (
COUNT ( Tickets[Case] ),
FILTER (
Tickets,
Tickets[StartPeriod] >= MAX ( Project[StartPeriod] )
&& Tickets[StartPeriod] <= MAX ( Project[EndPeriod] )
)
)
Measure2 =
CALCULATE (
COUNT ( Tickets[Case] ),
FILTER (
Tickets,
Tickets[StartPeriod] <= MAX ( Project[EndPeriod] )+30
)
)smpa01 For Measure 2, I am not getting correct results.
Every Organization Starts with a Project Case, it may last upto N number of days depending upon how long it took.
But I need to know the Cases submitted in the First 30 days when the Project Case got Closed(EndDate)
For Ex: If Project Case took the Entire August Month to complete and get Closed(8/31/2021). I need to know the Cases submitted in the Month of September (30 days).Anonymous my solutuion was based on the data provided.
Improve your question with better sample data if you are not getting the result you desire.
smpa01 I just updated the dates for the case
Project TableProject case Organization StartPeriod EndPeriod 42251 XYZ 8/6/2017 8/31/2017
Tickets TableCase Organization StartPeriod EndPeriod 52454 XYZ 8/28/2017 9/6/2017 56464 XYZ 9/6/2017 9/18/2017 54859 XYZ 9/7/2017 9/26/2017 52421 XYZ 9/8/2017 9/9/2017
Here, when you look at the dates. The project case for Organization XYZ was started and completed in August on 8/31/2017.
For Measure 2, I want the cases from Tickets Table where the StartPeriod is after 8/31/2017 for first 30 days (i.e from 9/1/2017 to 9/30/2017)
Makes Sense?
Any recommendations Greg_Deckler amitchandak