Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
alecsonline
Helper I
Helper I

Calculate values on a table with "previous" years and a reading date

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")

 

Reservations Data.JPG

 

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.

 

Relations.JPG

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 !!!!!

Cattura.JPG

 

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"; 

 

 

Cattura3.jpg

 

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: Cattura2.JPG

 

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????

 

Cattura5.JPG

Thank you all!

 

1 ACCEPTED SOLUTION
v-danhe-msft
Employee
Employee

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:

1.PNG

When you slide the data, you could get the result:

2.PNG

Hope it could help you.

 

Regards,

Daniel He

 

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-danhe-msft
Employee
Employee

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

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
v-danhe-msft
Employee
Employee

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:

1.PNG

When you slide the data, you could get the result:

2.PNG

Hope it could help you.

 

Regards,

Daniel He

 

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.