cancel
Showing results for
Did you mean:
Regular Visitor

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

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?

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

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

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

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:

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.
2 REPLIES 2
Community Support Team

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

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:

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.
Community Support Team

Hi @alecsonline,