cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ashwini12
Microsoft
Microsoft

I want to dynamically count distinct IDs with latest AuditCreated dates based on date slicer.

var maxValue =
CALCULATE(
MAX('View_AllHelpToolRequestsWithAudit (2)'[AuditCreatedDate]),
ALLEXCEPT('View_AllHelpToolRequestsWithAudit (2)','View_AllHelpToolRequestsWithAudit (2)'[Id]
),
DATESBETWEEN('View_AllHelpToolRequestsWithAudit (2)'[AuditCreatedDate],[Start Of Previous Period],[End Of Previous Period]),
DATESBETWEEN(Dim_Date[Date],[Start Of Previous Period],[End Of Previous Period]),
'View_AllHelpToolRequestsWithAudit (2)'[AuditNewStatusId] <> 3 || 'View_AllHelpToolRequestsWithAudit (2)'[AuditNewStatusId] <> 4 ,
'View_AllHelpToolRequestsWithAudit (2)'[AuditIsActive] = TRUE()
)
VAR colmax= MAXX('View_AllHelpToolRequestsWithAudit (2)','View_AllHelpToolRequestsWithAudit (2)'[AuditCreatedDate])
 
return

 

COUNTX(FILTER(VALUES('View_AllHelpToolRequestsWithAudit (2)'[Id]),maxValue <=colmax),'
View_AllHelpToolRequestsWithAudit (2)'[Id])
-----------------------------------------------------------------------------------------------------
I want to check only those records whose latest AuditCreated date range is between date range [Start Of Previous Period] to [End Of Previous Period]

---------------------------------------------------------------------------------------------------------------------------

Consider below scenario:
slicer  date range - 11 june to 11 july


ID  AuditCreatedDate Status IsActive
1     12 June                 open     True  
1     11 June                 open     True ---- max (+1)
1     10 June                 open     True
2     12 Aug                  open     True         
2     10 July                  open     True --- max (+1)
2      9 June                  open     True
3      13June                 open     True 

3      10 June                open     True -- ---max(+1)
3      6 June                  open     True


Ans: Previous Open ticket count = 3



2 REPLIES 2
daxer-almighty
Solution Sage
Solution Sage

Please rephrase the problem in clear, consise and comprehensible terms. Then you'll almost guaranteed to get an answer. This can help you: https://community.powerbi.com/t5/DAX-Commands-and-Tips/How-to-Get-Your-Question-Answered-Quickly/td-...

 

@daxer-almighty  Rephrased. 

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Top Solution Authors
Top Kudoed Authors