Hi Everyone,
I am reaching out because one of the calculate I created, which includes 2 date filters, is not working.
A bit of context on what I am trying to achieve:
The company I am working for is tracking the Opportunity win and loss percentage for their opportunities and comparing the same with the previous period last year.
Now, the tricky DAX is on the calculation for the Opportunity won (they call it PO closed successfully)- the way they calculate it can be considered odd. The ask is "if I am checking all of the opportunities in Jan 2022, what was the value of those closed at the end of Jan2022 (or before) and how can I compare it with the same period last year?" - the logic for this example would look something like:
Total Opportunity: PO due date from 1st of Jan 2022 to 31st of Jan 2022
PO received: PO due date from 1st of Jan 2022 to 31st of Jan 2022 and PO received date from 1st of Jan 2020 to 31st Jan 2022
Total Opportunity Past period: PO due date from 1st of Jan 2021 to 31st of Jan 2021
PO received Past period: PO due date from 1st of Jan 2021to 31st of Jan 2021 and PO received date from 1st of Jan 2019 to 31st Jan 2021.
As you can imagine, this brings to the table 2 dates: PO due date and PO received date.
To achieve this, we will need 4 measures:
Total Opportunity current period.
PO closed successfully current period.
Total Opportunity Same period last year.
PO closed successfully Same period last year.
My problem is with the latter.
The formulas that I am using are (Notice that Date table is connected to PO due date):
Total Opportunity (working) = CALCULATE(
SUM('Report'[Sales value]),
'Report'[RPM Status]="Assigned"|| 'Report'[Status]="Closed-PO Received"|| 'Report'[Status]="Closed-Not Renewed"|| 'Report'[Status]="Quote Sent"|| 'Report'[Status]="Future Renewal"|| 'Report'[Status]="No Response"|| 'Report'[Status]="In Progress"|| 'Report'[Status]="On Hold"|| 'Report'[Status]="Potential Action")
PO closed successfully current period (working) = CALCULATE(SUM('Report'[Sales value]),
FILTER('Report', 'Report'[Status]="CLOSED-PO Received"),
FILTER('Report','Report'[PO Received Date]> MIN('Date'[Date])-600 && 'Report'[PO Received Date]< MAX('Date'[Date]))
)
Total Opportunity Same period last year (working) = CALCULATE(
SUM('Report'[Sales Value]),
'Report'[RPM Status]="Assigned"|| 'Report'[RPM Status]="Closed-PO Received"|| 'Report'[RPM Status]="Closed-Not Renewed"|| 'Report'[RPM Status]="Quote Sent"|| 'Report'[RPM Status]="Future Renewal"|| 'Report'[RPM Status]="No Response"|| 'Report'[RPM Status]="In Progress"|| 'Report'[RPM Status]="On Hold"|| 'Report'[RPM Status]="Potential Action",
SAMEPERIODLASTYEAR('Date'[Date]))
PO closed successfully Same period last year (NOT WORKING) = CALCULATE(
SUM('Report'[Sales Value]),
' Report'[RPM Status]="Closed-PO Received",
SAMEPERIODLASTYEAR('Date'[Date]),
FILTER('Report','Report'[PO Received Date]> MIN('Date'[Date])-1080 && 'Report'[PO Received Date]< MAX('Date'[Date])-365
))
This last one really doesn't work, as it shows blank but I can't figure out why. What would you do?
Now, I know its not the most elegant DAX writing, and I am more than happy for any feedback - I am completely stuck!
