topic Re: Returning counts on one date, filtered by another date. in DAX Commands and Tips

Returning counts on one date, filtered by another date.

spencerh998 — Fri, 21 Apr 2023 22:16:39 GMT

Hello,

I have two tables, a date reference table and a leasing table.

The leasing table has 2 fields- an application date, which lies in the past, and a lease begin date, which usually lies in the future. I am attempting to build a visual that can show when leases are starting based on when the application was completed.

I have an App Date and a Lease Begin Date in the leasing table, and a single date reference in my date table. The active relationship between the two tables is on app date, and I have an inactive connection on lease begin date.

The goal is to be able to select a prior period (ie, last 7 days) and show the count of begin dates in a graph that correspond with those applications.

The dax I am currently attempting to use is:

Future Leases = CALCULATE( CALCULATE(SUM('DSR - Lease Detail'[App]) ,

USERELATIONSHIP('ALL - Date Reference'[dateday] , 'DSR - Lease Detail'[BeginDate])

) , CROSSFILTER('ALL - Date Reference'[dateday] , 'DSR - Lease Detail'[App Date] , OneWay_LeftFiltersRight))

For reference, the field "App" is a static field of 1 built into my query to quantify applications. The goal of the sum is to get the number of applications (in this case leases starting) in a time window.

However, this is only returning begin dates of leases and is not filtering on the application dates. I have also attempted using multiple userelationships in the function.

My example set of applications / begin dates is:

In theory, if I were to select an app date of the last 7 days, my graph that uses the measure should return a value of 1 if the date column is for May.

Is this function possible to build?

Re: Returning counts on one date, filtered by another date.

Wilson_ — Sat, 22 Apr 2023 18:55:53 GMT

Hello Spencer,

Can you please share your pbix? It would be infinitely easier to work with (at least for me).

Also, to clarify what you're trying to accomplish, are you envisioning one slicer for app dates (ex: applications in the last seven days) and one slicer for lease start date (ex: leases starting in May) and looking to return a count of the intersection of the two slicers (ex: applications in the last seven days that have a lease start date in May)?

Re: Returning counts on one date, filtered by another date.

spencerh998 — Mon, 24 Apr 2023 14:42:15 GMT

Hi Wilson,

Thanks for your time! I unfortunately canot share the PBI file itself due to data policies within my organization.

What you described for what I am envisioning is spot on, though. If there is any additional information I can provide I'd be happy to support.

Re: Returning counts on one date, filtered by another date.

Wilson_ — Mon, 24 Apr 2023 16:33:55 GMT

Spencer,

Feel free to make a sample pbix with mock data and share that instead. 🙂

Re: Returning counts on one date, filtered by another date.

spencerh998 — Tue, 25 Apr 2023 14:45:05 GMT

The sample data set is a great idea, thank you! What is the best way to share a PowerBI on these forums? I have an example pbix file built out now, but I am unsure what would be the best way to give you access.

Re: Returning counts on one date, filtered by another date.

Wilson_ — Wed, 26 Apr 2023 15:53:56 GMT

Spencer,

You can upload it to something like Google Drive or Dropbox and share the URL here.