Need help finding the right function and syntax in PBI report

[MattAdams]

I have two tables (date, and issues table). The Date table has sequential dates for reference from 2016 to 2020 in order. My issues table has a list of work incidents, namely with a "closed date" and a "opened date". I would like to figure out how I can write a custom column to basically say "for each calendar date on the date table, did that calendar date  fall between the opened date and the closed date, if so, 1, else 0. Kind of complicated, which is why I'm bouncing it off you experts. Thanks for your consideration.

[Ashish_Mathur]

Hi,

Your question is not clear.  Show both tables and the expected result.

[MattAdams]

Hi....

Below is the pic of the 2nd (issues) table. Showing the resolveddatewithnulllogic column. There's another column named opened date. I'm wanting to compare against the "calendar_date" rows in the date table (shown in first post) and say for each row in the task table, for between the opened date and resolveddatewithnulllogic was it open for each calendar date by day and if so mark a 1 operator, if not a 0 operator. Does that clear my question up? The goal is to see what issues were open for each calendar_date day in the date table to look at open tickets (issues).

[Greg_Deckler]

Hey @MattAdams, can I inquire as the how you are expecting to visualize this? Is what you are trying to achieve that you could click an issue and then have a table of all of the dates that issue was open? Or are you trying to aggregate this in some way? Trying to understand the use case. 

[MattAdams]

It will be shown with a area chart/graph, showing the count of open issues by day or week....This will ultimately show a trend when the count by time buckets (1-2 days, 2-5 days, etc) rise in count by day or week, or drop. This ultimately shows how much work we are getting done or not done. Does that help? Its basically to show aging work and how its getting done as days and weeks progress.