Help Variable date measure

Question

Hi Everyone,&nbsp;Need your help with something - Not even sure if it's possible to achieve this.&nbsp;I have the next table with 3 columns:&nbsp;Customer&nbsp; &nbsp;|&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Booking Date&nbsp; &nbsp; &nbsp; &nbsp;| Booking ReferenceA&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;01/01/2024&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ABC1A&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 02/01/2024&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; DDD1A&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 7/01/2024&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;4567B&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 10/02/2024&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CDB2&nbsp;Main issue is that the periods below needs to be changed somehow manually by the customers and I'm not sure how I can create a measure based on X and Y dates, however this to reflect on the dates the customer choose or entered in a filter or something like this in PBI&nbsp;Now I'm trying to find out two things:- we offer discounts for booking made in X period so trying to find out in that period how many bookings we've made per customer (I.e period 01/01/2024 - 02/01/2024)- also, trying to find out in the Y period (usually this is 7-10 days after the above period, so 03/01/2024 - 10/01/2024) how many bookings we've had per customer&nbsp;This will result for customer A in 2 for first period (period with discounts) and 1 bookings in the period without discounts.I also need to see the difference in percentage between the periods - this will show a 100% increase (2-1 / 1)&nbsp;CustomerDiscountwithout discountvarianceA21100%&nbsp;Is there a way to create a variable measure for the dates? This will need to store 2 variable dates, one for "period with discounts" and another for "period without discounts"&nbsp;Thank you for your help!&nbsp;

daniel29195 · Answer

hello&nbsp;Georgetimes&nbsp;im confused about&nbsp;how to tell if it has discount or not .what is the condition .&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

georgetimes · Answer

Hi&nbsp;Daniel29195&nbsp;&nbsp;Sorry, I wasn't clear enough. The issue is that we don't store anywhere this data. We just agree with customers that from period X you'll have a discount of 10% for example.&nbsp;That's why I need a filter or something like this were the big managers can come, add X dates for "period with discount" and Y dates for period without discount.&nbsp;I know this can be done with two filters and two tables so I'll get:table 1&nbsp;CustomerDiscountA2&nbsp;&nbsp;table 2Customerwithout discountA1&nbsp;But I don't know how I can store those numbers in order to calculate the difference (2 minus 1) divided by 1&nbsp;

daniel29195 · Answer

Georgetimes&nbsp;ok i think i got you .&nbsp;&nbsp;create a table to store this data as follow :&nbsp; ( example&nbsp; name : Discount_tbl)&nbsp;let us say , discount 10% between 2023-01-01 and 2023-01-10&nbsp;&nbsp;then your table would be as follow :&nbsp;date&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;discount&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;discount_status2023-01-01&nbsp; &nbsp; &nbsp; &nbsp;0.1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; with discount2023-01-02&nbsp; &nbsp; &nbsp; &nbsp;0.1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;with discount2023-01-03&nbsp; &nbsp; &nbsp; &nbsp;0.1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;with discount2023-01-05&nbsp; &nbsp; &nbsp; &nbsp;0.1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;with discount.....2023-01-11&nbsp; &nbsp; &nbsp; &nbsp; 0&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; without discount&nbsp;&nbsp;&nbsp;from here, you should have a dimdate which should be linked to this table .&nbsp;A dimcustomer&nbsp; =&nbsp; distinct(table_name[customer-column]) ( you can use this to create w dimcustomer if you dont have it in your model ) .&nbsp;&nbsp;and your table :&nbsp;Customer&nbsp; &nbsp;|&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Booking Date&nbsp; &nbsp; &nbsp; &nbsp;| Booking ReferenceA&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;01/01/2024&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ABC1A&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 02/01/2024&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; DDD1A&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 7/01/2024&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;4567B&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 10/02/2024&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CDB2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;from here, you can create a calculated column in your table :&nbsp;discount_status =&nbsp; related ( discount_table[discount_status])now you can drag and drop your columns into the visual and it will show what you want .&nbsp;&nbsp;&nbsp;&nbsp;If my response has successfully addressed your issue kindly consider marking it as the accepted solution! This will help others find it quickly. I would appreciate hitting that kudos&nbsp;👍🫡&nbsp;

georgetimes · Answer

Hi&nbsp;Daniel29195&nbsp;&nbsp;Thanks for your answer, however I'm afraid this doesn't quite help me.The reason why I'm stuck it's because of those discount dates. I can't have another table that says "discount" "not discount" because I need this to be fully variable.&nbsp;The customer will want now to see discount date between 01/01/2023 - 15/03/2023 and 1 minute after he sees the numbers he'll want to check another discount period of 23/03/2023 - 27/03/2023 for example.Those discount and not discount dates are not "fixed dates" for one report. Hope it makes sense.&nbsp;

daniel29195 · Answer

Georgetimes&nbsp;&nbsp;are you able to share your file,&nbsp;and show me a visual example of what you need,&nbsp;and i will try to help you .&nbsp;&nbsp;&nbsp;best regards

Forum Discussion

Help Variable date measure

6 Replies

Related content

Performance - Multiple measures and variables

dynamic library variables

When to use variables vs measures

Use variable in measure

Measure

Customer	Discount	without discount	variance
A	2	1	100%

Customer	Discount
A	2