Forum Discussion
Filters and Calculated Meassures using a fact table with different Dates - (Reservationslist)
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
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