Calculate Completed Refunds – DAX Challenge

Here is another interesting challenge to burn your grey cells!

 

Calculate Completed Refunds – Video

 

The Case

I am dealing with a simple sales and refunds data. Consider this example..

Before I deep dive into technicalities, here is the business summary in English

For each sales transaction there can be multiple instances of refunds until all units are refunded.

 

Now consider this simple Data Model

1. Since refunds table can have a duplicate transaction ID, It becomes the fact table for Sales (where Tran ID is unique).
2. Slight Nuance – each refund instance is also marked with a status (i.e. Complete or Pending)
3. The Calendar and Products are two standard lookup tables.

 

The Problem

Calculate Refunded Amount where the status for all instances are marked as “Complete” for each transaction.

Appendix – Consider the two highlighted Transaction IDs – One where all instances are marked complete and the other where it’s not.

 

Ideal Output

Your final output when shown across the Year and Month should result like this.

 

Rules

1. Feel free to use Power Query to reshape / remodel the data.
2. You should produce a measure to calculate Completed Refunds (in $ Value terms)
3. Your measure should be sliceable by any lookup table connected to refunds.
4. If you answer is more complex than a single measure, please consider posting a link to download your pbix file.

 

Download Data

 

Get going! Big shout out to everyone who posts the correct solution

---

 

Calculate Refunds Solution

 

Download Solution File