Filtering a date field by another date field

[noah44]

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]

@parry2k 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' 

[parry2k · Accepted solution]

@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 Solutio...

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.⚡

[noah44]

@parry2k thanks for the fast help!

This worked right away