Hello All,
I have reached the end of my capabilities it seems. I have an urgent request for help in creating DAX. In essance the user specifies a date range and if the contract is in that date range add that contract to the display and show Contract Name, Total Price, Annual Cost. I have thought about making a list of months/years betwen FromDate and ToDate and another list of months/years between RequestedStartDate (RSD) and RequestedEndDate(RED) and using some sort of IF is in function, but haven't gotten very far down that road yet.
The logic I am trying to create in DAX is as follows:
Variables input by user
(not really sure how to capture this either, I have tried using the Date Range slicer based on a date table not connected to anyother table but SELECTED or SELECTEDVALUE [Start] and [End] doesn't seem to work)
Requested Start Date (RSD)
Requested End Date (RED)
// eliminate any contracts which start after the contract period requested
If the RSD is > To Date, skip record
// eliminate any contracts which end prior to the contract period requested
If the RED is < From Date, skip record
If Full Contract
If From Date (Year) = RSD (Year) and To Date (Year) >= RED
AnnualCost = Total Price
Else
AnnualCost = $0
If Annual or Annual Variable Payment
//Calculated Contract Years (CCY)
If FromDate.year = ToDate.year
CCY = 1
Else
CCY = ToDate.year - FromDate.year (int)
If CCY = 1
AnnualCost = TotalPrice (Float)
Else
AnnualCost = TotalPrice/CCY (Float)
| Contract Name | Payment Schedule | Total Price | Annual Cost | From Date | To Date |
| Widgets | Annual | 60,000.00 | 60,000.00 | 7/15/2019 | 7/15/2020 |
| Gizmos | Annual | 80,000.00 | 20,000.00 | 6/01/2018 | 6/01/2021 |
| Do-Dads | Full Payment | 200,000.00 | 0* | 01/01/2016 | 01/01/2021 |
*0 will show if RSD.year is after FromDate.year else Total Price will be shown
