Forum Discussion
Help Variable date measure
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 👍🫡
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.
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
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