Calculate discount based on dates variable in PowerPivot

[Kalle123]

Hi!

I have two tables in my PowerPivot (see example below). The first one (Table A) shows different packages at different hotels and how much they cost. The second table (Table B) shows how much discount we can get from the different hotels. The discount is based on when the guest are staying there. So if we have a guest arriving the 1 of May at hotel A and taking the deluxe package for 7 days, they will get in total discount of 100 euros.
Calculation:
-10 € * 3 days (1/5-3/5)
-15 € * 2 days (4/5-5/5)
-20 € * 2 days(6/5-7/5)
=100 €

My big question is; How can I make a Dax-formula that will calculate how much discount the customer will get automatically?

I have only started with the formula =Calculate(sum(TableB[Discount]);Filter(TableB;TableA[Hotel] =Tableb[Hotel]);Filter(TableB;TableA[Package] =TableB[Package])).
The troubles comes when I want to make the dates as filters and calculate the dates between the different periods.

Hope that you guys can help me!

Thaaaaank you!

Table A
Date House Hotel Package Duration Price Total Discount
2016-05-01 A Standard 7 1000 ???
2016-05-01 A Deluxe 7 1500 ???
2016-05-16 B Standard 14 2500 ???
2016-05-16 B Deluxe 14 3000 ???
2016-05-30 A Standard 7 1500 ???
2016-05-30 A Deluxe 7 2700 ???
2016-06-13 A Standard 14 2600 ???
2016-06-13 A Deluxe 14 5000 ???


Table B
Hotel Package Discount Stay date from Stay date to
A Deluxe -10 2016-04-01 2016-05-03
A Deluxe -15 2016-05-04 2016-05-05
A Deluxe -20 2016-05-06 2016-06-30
A Standard -25 2016-05-01 2016-05-05
A Standard -20 2016-05-06 2016-06-30
B Deluxe -30 2016-04-01 2016-05-20
B Deluxe -15 2016-05-21 2016-06-30
B Standard -20 2016-05-01 2016-06-30

 

[Matt Allington]

This is non trivial for a DAX novice. The approach you should take is this.
1 create a calendar table and join it to table A on date. Do not connect the calendar to table B
2 create lookup tables for all other common fields. Join both A and B to those new lookup tables.

This is will give you a set of lookup tables that you can use in your pivots. These will filter both of your data tables - except the calendar table will not filter table B.

You should then be able to write DAX to calculate measures such as total length of stay, full price before discount, count of stays per hotel, count of stays by package, total room nights by package, hotel etc. All these are straight forward.

The issue is that there is no immediately obvious way to connect the calendar table to the discount table. The solution is to simulate the relationship using a filter. Now the solution will depend on the data in the discount table eg, can there be 2 discounts for the same room at the same time? I will assume not but I think the formula will work anyway.

So you want something like this. (It will be hard for me to get this right first time without having a working prototype to work with).

Assuming [Full Price] is a measure that calcs the full room rate for the stay

Total Discount = sumx(calendar,calculate([full price] * max(TableB[Discount]),filter(TableB,max(calendar[date]) >= TableB[from date] && max(calendar[date]) <= TableB[to date])))

the idea of this formula is as follows.
The sumx is needed to iterate over each day in the period. Each day needs to be evaluated individually as the discount either applies or it doesn't on a daily basis.
The filter function simulates a relationship between the calendar table and the discount table. the 2x max(calendar[date]) bits of the formula "harvest" the current date being iterated by sumx. There is only ever 1 date in each iteration, so max() just returns that date. Min() would also work. So read the filter portion as "filter TableB so that it only shows rows where the current date being iterated in sumx is on or after the start date of a discount and on or before the end date". In otherwords, the current date is in the discount period. after the filter is applied If there are no rows that survive the filter, then [full price] * max(TableB[Discount]) will equal zero. If there is 1 row that survives, then [full price] * max(TableB[Discount]) will equal the cash value of the discount. If there is more than one row, then the max of the values is taken.

I hope it works and hope it makes sense.

 

[Kalle123]

Dear Matt! Thank you for you quick replay!

So first of all I want to add some information regarding the case. There is only 1 discount per period, so there can never be 2 or more discounts at the same time. I would like to have a column i TableA that calculates the Discount for each package and duration. Because I will later use the information together with other information.

Date________Hotel______Package_____Duration______Price_____Total Discount____LookupValue____Home date______Full Price
2016-05-01__A_________Standard_____7___________ 1000_______ ???__________AStandard______2016-05-08_____7000
2016-05-01__A_________Deluxe_______7 ___________1500_______???__________ADeluxe________2016-05-08_____10500
2016-05-16__B_________Standard_____14___________2500_______???__________BStandard______2016-06-01_____35000

I have now Created a calendar table and joind it to table A and created a lookupvalue i both TableA and B.

If I paste your formula I get following message:
The value for column 'Full Price' in table 'TableA' cannot be determined in the current context. Check that all columns referenced in the calculation expression exist, and that there are no circular dependencies. This can also occur when the formula for a measure refers directly to a column without performing any aggregation--such as sum, average, or count--on that column. The column does not have a single value; it has many values, one for each row of the table, and no row has been specified.

If I removed the Full price from the formula I just got a blank response. I tried following formula
= sumx(calendar;calculate(max(TableB[Discount]);filter(TableB;max(calendar[date]) >= TableB[from date]))) and then I got -3090 on all rows. I seems that It calculates how many rows there are in the calendar (no matter what I have for dates in TableA) and then multiply it with the largest discount number it can find in TableB which doesn't feel right.

I have probably done something wrong in the joins or in the formulas... I am a rookie at PowerPivot and have learnt everything by myself so I have probably done some simple mistake.

When I link the calendar table to Table A, I created a relationship between Date in TableA and Date in Calendar. But how can I link the last date going home in calendar?

Best regards!

 

[Matt Allington]

[Full Price] is supposed to be a measure, not a column. Replace [Full Price] with sum(TableName[Full Price])

If it doesn't work, it will be easier if you share a sample workbook so I can take a look.

 

[Kalle123]

Hi!

Thanks again!

I replaced it with TableA[FullPrice] but it still does not work. How can I share my workbook with you? It just seems like I can link to a URL here and not post any workbook..

 

[Matt Allington]

I sent you a link to upload yesterday but I haven't heard from you. I just noticed that what you posted back is not what i suggested. You are using a column (well that is what it looks like). it needs to be an expression or a measure. eg SUM(TableA[FullPrice])

 

[Matt Allington]

I have taken a look at the file and sent an update. There are quite a few issues and considerations I hadn't understood.

Firstly your discount column is $$ rather than % as I understood. I changed it to % discount and made some changes to the formula and got a working result. However it is working based on my understanding of what was required, but it is not really working for the reality of your data.

Your table structure is complex for DAX to manage. The bookings table has 1 record with a start date of the booking and the duration of the booking. So it is therefore possible (even likely) that the discounts will only partially overlap. It is not possible to write a simple formula that checks the start date with the discounts table to work out the discount. If you think about the logic, there needs to be a formula that checks the date in table A and also the duration. It then needs to go to the discount table and iterate of the discount table X times where X = duration. It needs to extract the discount for each date (if any) and then multiply this discount (assuming the discount is a %) by the daily full rate. I'm not saying it can't be done, just that it is not simple. Plus I don't want to invest time trying to solve it without knowing more.

If the discount in your discount table really is $$, then I have no idea how the logic of that would work. Does the first booking get all the discount, or a portion of it, or was that a mistake?

This looks like quite some complex work to me. I am happy to chat further but can't commit to the time needed to get this working (in my free time). If you would like more help, I suggest you start by explaining where this data comes from, why it is in the format it is in, why it is in 2 different tables and why you need to combine it. Good DAX solutions always start with "why".

 

[Kalle123]

Hi Matt!

Thank you for all the help!

So a little more information about the date and my why: So all the information comes for our database. The company I work for sells pre-packaged hotel-nights. The system stores all the prices as package prices and not prices per day. Now I want to calculate how much we earn on the different packages. So first I collect the hotel-cost for the package and then I want the deduct an Early booking discount that we get. In our system we enter the Early booking discount as it Shows in TableB. That between two stay-dates we get XXX € in discount per day and between other dates you get a different discount per day. So the reason why we have different format on the data is because it is stored differently in the system. It is two different systems that I want to join together to make a forecast on contribution.

Calculation of Deluxe Package at Hotel A, arrival 1/5 and stays for 7 days.
-10 € * 3 days (1/5-3/5)
-15 € * 2 days (4/5-5/5)
-20 € * 2 days(6/5-7/5)

Hotel______Package_______Discount/day___Stay date from_______Stay date to
A________ Deluxe ________-10 €________2016-04-01________ 2016-05-03
A________ Deluxe________ -15 €________2016-05-04_________2016-05-05
A ________Deluxe________ -20 €_______ 2016-05-06________2016-06-30

But perhaps the easiest way is to make a TableC where all the dates, hotel and packages (deluxe or standard) are listed and the calculate the discount per day/hotel/package there. And then sum up the information of TableC to TableA. It will be a very long list in TableC but It seems like it is the easiest way to go.

But thank you again for all tour assistance!

 

[Matt Allington]

I am thinking that you should not use Power Pivot to solve this problem. The first step in developing a good Power Pivot report is to make sure your data is well "shaped" for the task at hand. The reason this is so complicated is that this looks like database work to me - not power pivot reporting work. Do you have a database tool you can use? Or maybe you could do it in Power Query - not sure. There is also a lot of detail to try to understand (effort from the helper) to direct you, and given the complexity of the problem, it is only realistic to even try to help if you can provide some realistic data in the exact table formats of the source data (desensitised is fine and loaded into Excel is also fine - as long as the tables are identical). Even then the effort to understand may be greater than someone here (for free) is willing/able to give. I will take a look if you post some samples, but no guarantees. To increase the chance of me understanding, you should show some worked through examples in the file how it all works.

Sorry I can't help more

Matt