Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Getting crazy about this, at the end I have to give up and ask your help to write necessary formulas.
Solving this rebus would be the key for the entire report.
Here you can access the pbix file: https://1drv.ms/u/s!Ar-kpjLj8CF4jFoWSd4_WMq2uYIl
The data table is a file with room reservations details: date (the date the room is reserved for), number of rooms reserved, reservation date and cancellation date if the reservation is canceled. Two indexes: onrequest (can be "c" or "o") and Status (can be "cxd" or "active")
As you can imagine I have to see the rooms reserved during the year.
That tabel is connected to a calendar by the dates. I have one more calendar that represent the "date of lecture", not connected.
No problems for the static data: need to know how many rooms per night with "onrequest"=C and "status"=active
Here we are:
Rms cfd = CALCULATE(SUM(Reservations[roomsnumber]);FILTER(Reservations;Reservations[Status]="attiva");FILTER(Reservations;Reservations[OnRequest]="c"))
No prroblems to know how many rooms were reserved at a certain "reading date": i.e. how many rooms were booked on December 12th, 2017?
maxreadingdate = MAX(ReadingCalendar[ReadingDate])
Rms cfd with reading date = CALCULATE(SUM(Reservations[roomsnumber]);FILTER(Reservations;Reservations[Status]="attiva");FILTER(Reservations;Reservations[OnRequest]="c");FILTER(Reservations;Reservations[ReservationDate]<[maxreadingdate]))
It works !!!!!
BUT I NEED MUCH MORE! I need to see the rooms booked this year Vs the previous years....
In the followin report the columns header are the "how many years before";
It works!
I can choose from the list which "many years before" to calculate.
The formula is:
Rms cfd xxx years ago =
var previousyears = MAX(PriorYears[Howmanyyearsbefore])
return
CALCULATE(SUM(Reservations[roomsnumber]);Reservations[Status]="attiva";Reservations[OnRequest]="c";DATEADD(Calendario[DATA];-(previousyears);YEAR))
The table "PriorYears" is simply this:
But I am not able to go further!
This is where I need someone to help me to obtain this: to see how many rooms were booked the previous years, for the same day of the year at the same "reading day".
I.E. for the 2018 how many rooms were already booked on June 21st 2018? And which was the situation for the 1 year before reading it 1 year before? And 2 years before? etc. etc
It is simply adding the slicer below, as per following image.... what's the magic formula to obtain this????
Thank you all!
Solved! Go to Solution.
Hi @alecsonline,
From your description, could you want to use the Reading date to filter your data table? If so, you need to create relationships between 'Reading Calender' Table and 'Reservations' Table:
When you slide the data, you could get the result:
Hope it could help you.
Regards,
Daniel He
Hi @alecsonline,
Could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as Answered?
Regards,
Daniel He
Hi @alecsonline,
From your description, could you want to use the Reading date to filter your data table? If so, you need to create relationships between 'Reading Calender' Table and 'Reservations' Table:
When you slide the data, you could get the result:
Hope it could help you.
Regards,
Daniel He
User | Count |
---|---|
124 | |
106 | |
99 | |
63 | |
59 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |