Help - I need to calculate total revenue for a month but only for Clients with an Activity

[mishb77]

I have the following tables.   

Participants  -  All participants provides participant detail

DateTable -  Date table. 

Revenue  - details all transactions  by transactions date made by a participant  (looks up to Participant and Date Table)

Enforcement Actions- logs by date when an  Enforcemennt activity is carried out (looks up to Participant and Date Table)

What I am trying to achieve is I was know  how much revenue was collected  from  Participants the activity was carried out on within 30 days of enfocement Activity carried out.   I have a Date Slicer using the Date table to select the dates i'm interested in.   Such as From 05/09/2020 - 08/09/2020

This is the measure I've used :

Enforcement Action Collections 30Days = CALCULATE([Total Revenue Collected],FILTER(Participants,CONTAINS(EnforcementActions,EnforcementActions[participant_ID],Participants[Participant_ID])),DATESINPERIOD(DateTable[Date].[Date],MIN(EnforcementActions[Action_Activity_Date]),+30,DAY))

More than one enforcement activity can be carried out on a participant in the same period?  I'm not sure if this is causing any issues?

Can anyone please help me as this is not returning the right results?   Would be much appreciated. 

[mishb77]

Just to update.   Your calculation worked.   I had my REvenue Collections table linked up to Date table by enfocement date rather than transation date. 

[amitchandak · Accepted solution]

@mishb77 , try like

Enforcement Action Collections 30Days =
var _min = minx(allselected(DateTable),DateTable[Date])
return
CALCULATE([Total Revenue Collected],FILTER(Participants,CONTAINS(EnforcementActions,EnforcementActions[participant_ID],Participants[Participant_ID])),DATESINPERIOD(DateTable[Date],_min,+30,DAY))

[mishb77]

Thank you for your response. I tried that but it still returned transactions in November when the dates I had selected where March.