Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
There's probably a nice trick here, but I'm struggling with it. We are using the standard Date Range Slicer. The calendar date is connected to the create date on a Ticket Table. I cannot make an active connection between another date in a Document table and the calendar because it would create a circular relationship. I want to check a Start Date and an End Date in the Document table to see if these dates fall within the range of those selected with the Date Range Slicer to determine if a particular document should be included in a calculation. Does anyone know how to do this - to check these dates against those selected? Thanks in advance!
Solved! Go to Solution.
Hi @Shelley,
Check the answer I gave on this post in this case I'm calculating the number of days but the formula can be readjusted to give a YES / NO text instead. the adjusted formula is below:
YES_NO = VAR START_DATE = MIN ( DimDate[Date] ) VAR END_DATE = MAX ( DimDate[Date] ) RETURN IF( SUMX ( SUMMARIZE ( FactTable; FactTable[StartDate]; FactTable[EndDate]; "DAYS"; IF ( FactTable[StartDate] < START_DATE && FactTable[EndDate] < START_DATE || FactTable[StartDate] > END_DATE && FactTable[EndDate] > END_DATE; 0; DATEDIFF ( IF ( FactTable[StartDate] < START_DATE; START_DATE; FactTable[StartDate] ); IF ( FactTable[EndDate] > END_DATE; END_DATE; FactTable[EndDate] ); DAY ) ) ); [DAYS] ) > 0 ; "YES"; "NO")
In this case you will get the following result.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @Shelley,
Check the answer I gave on this post in this case I'm calculating the number of days but the formula can be readjusted to give a YES / NO text instead. the adjusted formula is below:
YES_NO = VAR START_DATE = MIN ( DimDate[Date] ) VAR END_DATE = MAX ( DimDate[Date] ) RETURN IF( SUMX ( SUMMARIZE ( FactTable; FactTable[StartDate]; FactTable[EndDate]; "DAYS"; IF ( FactTable[StartDate] < START_DATE && FactTable[EndDate] < START_DATE || FactTable[StartDate] > END_DATE && FactTable[EndDate] > END_DATE; 0; DATEDIFF ( IF ( FactTable[StartDate] < START_DATE; START_DATE; FactTable[StartDate] ); IF ( FactTable[EndDate] > END_DATE; END_DATE; FactTable[EndDate] ); DAY ) ) ); [DAYS] ) > 0 ; "YES"; "NO")
In this case you will get the following result.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsUser | Count |
---|---|
128 | |
109 | |
100 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |