How to access filter values to calculate a measure?

[RFy]

Hi everyone,
I'm reporting a table "bookings per booker_type" in a selected period. The period is a filter to the report page. The number of bookings varies on each day.  I want to display the average number of bookings per day during the selected period. The formula would be like "sum over bookings per day during the selected period / number of days in the selected period". I want to display that measure per booker type. 

The period is selected by a filter "is on or after" to "is before":


How could I calculate that measure so that it adapts its value when I change the selected period? October has 31 days while November will have only 30, for instance.

I found https://community.fabric.microsoft.com/t5/Desktop/Find-out-Value-of-filter-and-use-in-Measure/m-p/11... but didn't understand from that post how, for instance, to calculate the number of days of the selected period.

Thanks in advance & Kind regards
Ralf

[Lubna-Gafer123]

to calculate you need to 

[Lubna-Gafer123]

To calculate the average number of bookings per day, divide the total number of bookings by the total number of days in the selected date range.

You likely already have your total bookings measure. To determine the number of days, you can create two measures to capture the earliest and latest dates within your selected filters:

 

Min Date DL = CALCULATE(MIN(Date_Line[Date])) Max Date DL = CALCULATE(MAX(Date_Line[Date]))

These measures will automatically respect any filters applied to your data.

Next, find the difference (in days) between these two dates:

 

Days Range DL = DATEDIFF([Min Date DL], [Max Date DL], DAY

This measure returns the total number of days in your selected range.

[RFy]

@Lubna-Gafer123 
I tried your suggestion and receive the error message "Cannot find table 'Date_Line'"

What is missing?
Kind regards

Ralf

[Jeanxyz]

I would use aveagex() measure.

avg_booking_per_day =
AVERAGEX(
VALUES(Date_Line[Date]),
CALCULATE(COUNTROWS(fact_bookings))
)

[RFy]

@samratpbi @v-ssriganesh 

Thanks for your suggestions. I wasn't able to get back to the topic earlier.

I'll try your suggestions and let you know.

It might take the upcoming weekend to find the time for that.
Thanks for your kind understanding

Ralf

[v-ssriganesh]

Hello @RFy,

Hope everything’s going great with you. Just checking in has the issue been resolved or are you still running into problems? Sharing an update can really help others facing the same thing.

Thank you.

[v-ssriganesh]

Hi @RFy,

Thank you for posting your query in the Microsoft Fabric Community Forum, and thanks to @samratpbi for sharing valuable insights.

Could you please confirm if your query has been resolved by the provided solutions? This would be helpful for other members who may encounter similar issues.

Thank you for being part of the Microsoft Fabric Community.

[samratpbi · Accepted solution]

Hi @RFy ,

To find the average number of booking per day, you need to divide your total booking by total number of days.

Now I believe you already have the total booking. so to find the denominator, you need to find out the number of days range you have selected. For that what you can do is, simply create 2 measures to find the Max and Min dates.

Min Date DL = MIN(Date_Line[Date])

Max Date DL = MAX(Date_Line[Date])

The above measures will consider your filters also.

Then find the difference between those 2 dates:

Datediff DL = DATEDIFF([Min Date DL], [Max Date DL], DAY)

Then this is how it looks like:

You can simply divide your sum of bookings by date difference measure.

[side note: I would prefer to have a date slicer in the report itself, instead of using filter pannel]

Hope this helps to resolve your problem. If it does, then please accept this as solution.

Thanks - Samrat

[RFy]

Hi @samratpbi,
I tried your suggestion and receive the same error messages as with the suggestion from @Lubna-Gafer123 

What is missing?
Thanks in advance & Kind regards
Ralf

[samratpbi]

Hi @RFy Date_Line is a table I created for this example. Your table may have a different name. For you, I think whatever your table and column names are, use those. e.g. If your table name is Date_Dim and column name is event_date, then use Date_Dim[event_date].

Hope this helps.

If this helps to resolve your problem, then mark it as solution.

Thanks - Samrat

[RFy]

Thanky, @samratpbi , with this explanation, everything worked out fine. I accepted that as solution