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

Calculate Accrued Commission - 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.

Calculate Accrued Commission - Problem

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

 

Hungry for more DAX Puzzles?

 



Topics that I write about...






Download Smart Ebooks on
Excel and Power BI