Filters and Calculated Meassures using a fact table with different Dates - (Reservationslist)

[NicolasD]

Hi everyone, 

i have an understanding problem i think. I need to calculate a certain revenue amount based on the Check out date and the Created date. 

So i have a list of Reservations, each reservation contains dates for when the reservaation is created, the checkin date, checkout date and if happend a cancel date.

I made a Date Dim Table and my scheme looks like this:

I created a Calendar Table for Created and Checkout to have specific dates for filtering.

No when i do Measure, they work when i only apply a checkout filter, but when it comes to the Created.Date Filter, the Sameperiodlastyear and dateadd function are not working:

So my question is, how can i build the meassure for Total Room Revenue and filter them via the Created Date and Check out date and also have the YOY meassure and also Dateadd function for showing changes over 2 or 3 Years?


Last Measure i used was a combination from Filter(Values(..... and USERELATIONSHIP which didnt work, and i might even didnt understand but saw in a video someone using it to filter all values in a certain table.....

So the outcome should be 2 - 3 Meassures in a graph, where the user can easaly check the Revenue based on two sliders for when reservations where made for a certain check out timeframe. Example, show me the revenue of all reservations made in 01.04.2021 - 02.06.2021 with a Checkout date in June 2021. And of course the Change made against last year and the year before....

any help would be much appriciated.

Thank you in advanced

Nic

[Anonymous]

Hi @NicolasD,

Time intelligence function not suitable for a custom date field, complex relationship, or advanced filter operations. 

Auto date/time in Power BI Desktop - Power BI | Microsoft Docs

Perhaps you can try to use the date function to manually filter on records.

Time Intelligence "The Hard Way" (TITHW)  

Regards,

Xiaoxin Sheng

[NicolasD]

Dear @Anonymous , Thanks a lot for the Links, will go thru them.
@amitchandak thank you a lot for the explanation, i check again if all data field are date and not Date/Time, truns out the Cancelation date was linked to the date table and was Date/Time, i change it bit didn't had any effect.

In the meantime i think the issue is, when i use only the Calculate Sum Meassure with a relationship from checkoout to Date.DIm, i have no Issues to show all Measures and filter them with the Checkout.Date.Dim. All Sameperiodlastyear and Dateadd functions work properly.

If i know will filter all Revenue by Checkout with a certain Created Date, it will work for the Calculated Sum but not for the Meassures with a reference to the Time before like Sameperiodlastyear or Dateadd which is the problem of having a datefilter for CreatedAt which includes the year. 

So i made some new Rows in my Datetables for CreatedAt which turns out works well:

I made a list of Numbers for each Month-Day combination so i can filter on the Filtersection between certain dates, like here from 1. April - 2. June (Filter is: 401 - 602) . While its hasn't included a Year date it will work over the last years (SPLY and DateAdd) just fine. 
With the DateDIM.tbl. i make the filter for the period i wanne see. Here from 1 June - 31.Decembre 2021 and while i have the Userelationship between Checkout and DateDIM, it will show me the Correct values.
The CreatedAtDate shows me only the last twerlf month, which is a controll to see that i have filtered the right dates. CreatedAt Weeks shows me the weeks with corresponding Date for controll. If i would use one of those filters, the SPLY and Dateadd would not work, so i created also a row for the Weeks, No i can filter by Monthname, Weeknumber and even Datesbetween without no Isseu and also made a row where i calculate the differenz from a date untill today in the creatdAt table. With this, the actual date is 0 and the day yesterday is -1, the day before -2 and so fort... So i can make a filter for the last 21 days or 40 day...

I would be more happy, if i would have two filters, one for Checkout and one for CreatedAt and it will also make the correct referenze to the Years before and the right timeframes. It feels somehow wrong what i do here and i have a feeling i run into issues i dont see in the moment.

Any help or confirmaation if i'm on the right path are much appriciated.
Greetings,
Nic

[amitchandak]

@NicolasD , there few thing.

First of make all the time relation 1-M single direction.

Second use time intelligence on a measure that already uses userelationship

example

calculate( calculate( SUM(Sales[Sales Amount]),USERELATIONSHIP ('Table'2[CreateDate], 'Date'[Date])),DATESMTD('Date'[Date]))

or

Measure = calculate( SUM(Sales[Sales Amount]),USERELATIONSHIP ('Table'2[CreateDate], 'Date'[Date]))

measure 2=

calculate( [Measure ],DATESMTD('Date'[Date]))

Make sure non of these dates have timestamp, if they have (check by using datatype -datetime and a form that shows time )

If they have to convert to date and join with that column

Date = [datetime].date

when you are using two date slicers, they are filtering data of each other in the table. So check if that can be an issue.