Calculate Cost Amortized Till Date – DAX CHALLENGE

Recently during a consulting assignment I came across an interesting accounting problem. Although the problem that I had was riddled with layered complexities, but I filtered the core of it in the simplest form.

Try solving it!

Consider this simple data..

Subscription with Start and End Date and Total Cost

Here is the Problem..

Calculate the Total Cost Amortized till date for each subscription. If the subscription is for 90 days and the total cost is 180, the per day cost = 2 (i.e. 180/90).

Here is how the output should look like

1. I have a slicer on Year = 2019 and Month = Sept
2. Cost Amortized Till Date is = Total Cost of Subscription x ( Days Consumed Until End of September 2019 / Total Days of Subscription )
3. Obviously the numbers should change as per the slicer selection (for month and year)

Rules

1. This should be solved using DAX without modifying the data structure.
2. Feel free to create supporting relationships (like a Date Table etc..)
3. Leave a link to your PBIX and post your DAX code in the comments below

DOWNLOAD THE DATA

Some more of these DAX Riddles

1. Find Trained Employees who left
2. SUMX Riddle
3. Offset Payments against Receipts using FIFO
4. Find Days before Earliest Date
5. Count Unique Travel Dates