Forum Discussion
Helper IFiltering a date field by another date field
Hi,
I am working on a report to analyze activity on one of our apps.
I want to show the number of instalations in a specific date range e.g. "Last 15 Days".
The problem is that I am using a different date field for the filter than the one that I need to know when the app was installed.
Filter: LastUsage ; App installed: CreatedOn
The two date fields are in the same table, therefor a simple relation doesn't work.
I tried to export "CreatedOn" into another table but I wasn't able to get that to work.
Best regards
Noah
noah44 you should have a calendar dimension in your model and you can add one using my blog post here Create a basic Date table in your data model for Time Intelligence calculations | PeryTUS IT Solutions
Set relation on date column from calendar table with the date columns from transaction table, one relationship will be active and others with be inactive. For inactive relationships add a measure as below:
New Measure = CALCULATE ( COUNTROWS ( YourTable ), USERELATIONSHIP ( YourTable[SomeDateColumn], DateTable[DateCoumn] ) )In filter, use date from new calendar table, and in visual use above measure and that will do it., Add measures for each inactive relationship similar to the one shown above.
✨ Follow us on LinkedIn
Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
3 Replies
- parry2k
Super User
noah44 you should have a calendar dimension in your model and you can add one using my blog post here Create a basic Date table in your data model for Time Intelligence calculations | PeryTUS IT Solutions
Set relation on date column from calendar table with the date columns from transaction table, one relationship will be active and others with be inactive. For inactive relationships add a measure as below:
New Measure = CALCULATE ( COUNTROWS ( YourTable ), USERELATIONSHIP ( YourTable[SomeDateColumn], DateTable[DateCoumn] ) )In filter, use date from new calendar table, and in visual use above measure and that will do it., Add measures for each inactive relationship similar to the one shown above.
✨ Follow us on LinkedIn
Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
noah44parry2k after testing for a while I noticed that this solution doesn't do exactly what I need.
I want one Visual to show the number of new App installations in the time range. This number depends on the 'CreatedOn' - date.
All other Visuals base on the 'LastUsage' - date therefore I need to filter 'CreatedOn' by 'LastUsage'