My Dear Quarantined Fellas! It’s been a while since the last DAX Challenge. The one for today is a common accounting problem.
Turn on you DAX Mode and get to work..!
Consider this Sales Data
- Transaction level Sales Data
- Total Sales = Units x Price
- The Sales Channel can be Affiliate, Organic, Promotional
- 10% Commission (on Total Sales) is payable to only “Affiliate” Sales Transactions
The Problem
If you notice there are 2 dates
- Date of Sale – Date
- Date of Commission Payout – Aff Payout Date
Consider the highlighted transaction. The sale was done on 3 Jan but the 10% commission payout happened on 1st March.
That means the commission was accrued for the month of Jan and Feb and got off the books in Mar.
The task is to write a measure for Accrued Commission (i.e. commission earned by not paid yet)
Rules
- A Date Table & Total Sales measure has been created for you.
- You can create supporting measures if need be.
- You cannot modify the data structure or add more columns
- Post your answers (DAX Code) in the comments below.
- Your Measure should be slice-able by any column in the Sales and Date Table
DOWNLOAD QUESTION – POWER BI FILE
DOWNLOAD SOLUTION FROM BELOW
Hungry for more DAX Puzzles?
- Calculate Cost Amortized Till Date
- Set off Invoices from Receipts
- Find Trained Employees who Left
- My Favorite – The SUMX Problem
- Find Unique Travel Dates between 2 Dates
