Forum Discussion
Helper IDAX query to compare two columns from different tables
OK, I read that 3 times and I'm not following it. Can you explain it a different way? Sorry!
Community ChampionMattAdams- Sure, you could create a column in your Issues table that was simply something like
Issue Duration = [Issue End] - [Issue Start] * 1.
You could also use DATEDIFF. Then you could change your filter like:
Issues[Issue Start]<=[Date] && Issues[Issue End]>=[Date] && Issues[Issue Duration] > 2 && Issues[Issue Duration] < 5 )
Stuff like that. You can even make it really complex by mixing && (AND) and || (OR) clauses and put in parathesis and all kinds of stuff.
Helper IMany Thanks sir. One last question, then I think I accept as solution. Do you know of a formula I can reach over into my Issues table from my date table look at the open and closed date on the issue and refer back to the date table "calendar_date" of say 1/1/2018 and do a operator that will essentially says "how many were open this calendar_date between these two open and closed dates? I've been looking for one that I can reference from the date table to the issues table, and can't find one just yet for this. Maybe its just me.
- Greg_Deckler
OK, I read that 3 times and I'm not following it. Can you explain it a different way? Sorry!
- MattAdams
oh no worries! my biggest dillemma with this darn report is now that I can tell what issues were open for these calendar_date's, I'm trying to determine if they were in an open status for each calendar_date, and how many were open. We are trending by the "buckets" of time per day throughout the year. I can't effectively use the state of the issue because we are looking back in time and generally speaking they're all in a resolved state today, but looking back 3 months ago would like to show how many were not resolved yet, by using the resolved date and opened date. Does that help? I do have it counting the amount of incidents between the open and closed date successfully, but have a bigger number than is accurate by telling which are essentially not resolved yet and still an open work for those days. Does that help any?