Help Variable date measure

[Georgetimes]

Hi Everyone,

Need your help with something - Not even sure if it's possible to achieve this.

I have the next table with 3 columns: 

Customer   |         Booking Date       | Booking Reference

A                               01/01/2024                    ABC1

A                                02/01/2024                    DDD1

A                                7/01/2024                     4567

B                                10/02/2024                    CDB2

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

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

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)

Customer	Discount	without discount	variance
A	2	1	100%

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"

Thank you for your help!

[Daniel29195]

hello @Georgetimes 

im confused about 

how to tell if it has discount or not .

what is the condition .

[Georgetimes]

Hi @Daniel29195 

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.

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.

I know this can be done with two filters and two tables so I'll get:

table 1 

Customer	Discount
A	2

table 2

Customer	without discount
A	1

But I don't know how I can store those numbers in order to calculate the difference (2 minus 1) divided by 1 

[Daniel29195]

@Georgetimes 

ok i think i got you . 

create a table to store this data as follow :  ( example  name : Discount_tbl)

let us say , discount 10% between 2023-01-01 and 2023-01-10 

then your table would be as follow : 

date                   discount                     discount_status

2023-01-01       0.1                            with discount

2023-01-02       0.1                            with discount

2023-01-03       0.1                            with discount

2023-01-05       0.1                            with discount

.....

2023-01-11        0                              without discount

from here, you should have a dimdate which should be linked to this table .

A dimcustomer  =  distinct(table_name[customer-column]) ( you can use this to create w dimcustomer if you dont have it in your model ) . 

and your table : 

Customer   |         Booking Date       | Booking Reference

A                               01/01/2024                    ABC1

A                                02/01/2024                    DDD1

A                                7/01/2024                     4567

B                                10/02/2024                    CDB2

from here, you can create a calculated column in your table : 

discount_status =  related ( discount_table[discount_status])

now you can drag and drop your columns into the visual and it will show what you want . 

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 👍🫡

[Georgetimes]

Hi @Daniel29195 

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.

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.

[Daniel29195]

@Georgetimes 

are you able to share your file, 

and show me a visual example of what you need, 

and i will try to help you . 

best regards

[Georgetimes]

Hi @Daniel29195 ,

Don't know how I can attach a PBI, however please find two screenshots, one with a demo data and one with how this should look like.

The easiest I can explain what I want is that I'd like to see the difference in bookings between period A and period B and I'd like to filter it by brokers, however there's no fixed or preestablished period A and B and that's why I'll need a filter where I can select period A (I.e 01/01/2023 - 03/01/2023) and period B (I.e 05/01/2023 - 10/01/2023) - Please see below the details and hope it makes sense

I've tried to keep this simple, however there're multiple customers and multiple brokers, but essentially what I want to see is:

- number of bookings per customer based on first filter (this will be before discount) - the date filter will be amended manually as per PBI

- number of bookings per customer based on second filter (this will be after discount) - the date filter again will be amended manually

- another table (which is not in PBI screenshot as I can't manage to find a solution) - this table will show again customers in rows and a column (measure most probably) that will calculate the difference between the above 2 numbers (before discount and after discount)

There're 10 bookings in Jan 23 for example, all for same customer, however diff brokers.

What I want for example is select first period (before discount) - All Jan - and select after discount as all Feb

All Jan - 10 bookings (before discount)

All Feb - 7 bookings (after discount)

so I want in the table (that you can't see in PBI as I don't know how to make this) to see -3 (7 - 10) which will mean for managers the "discount" period was not successfuly

This will go a bit further as you can see I also have a broker filter. I would also want to check the above numbers based on broker:

if I choose broker 1:

 - all Jan - 7 bookings (before discount) for broker 1

- all Feb - 7 bookings (after discount) for broker 1

So the table will now show

Customer         bookings

    A                          0  (7 - 7)

If I go further and amend the dates for 01/01/2023 - 01/01/2023 (before discount) and 03/01/2023 - 04/01/2023 for broker 1 this will mean

before discount - 1 booking

after discount - 2 bookings

The table will now show Customer A,  1 Booking (2 - 1) so this will mean discount period was sucessfuly and we did 1 extra booking compared to date X

Hope this makes sense and sorry about the long explanation

Customer	booking date	broker
A	01/01/2023	1
A	02/01/2023	1
A	03/01/2023	1
A	04/01/2023	1
A	05/01/2023	1
A	06/01/2023	1
A	07/01/2023	1
A	08/01/2023	2
A	09/01/2023	2
A	10/01/2023	2
A	01/02/2023	1
A	02/02/2023	1
A	03/02/2023	1
A	04/02/2023	1
A	05/02/2023	1
A	06/02/2023	1
A	07/02/2023	1