topic Comparision of date in range of dates in DAX Commands and Tips

Comparision of date in range of dates

Anonymous — Mon, 28 Sep 2020 17:21:50 GMT

I am working on two tables Events & Events_Summary:

I want to compare an event date between the start date and end date of every cycle and then display the cycle name for every event. If there is no cycle on that date of the event, then we display 'No-Cycle'. Can anyone help me with any solution in DAX? Is there any lookup for this

My Data looks like this

Events

Events_Summary

Desired Output in Events_Table

Re: Comparision of date in range of dates

HotChilli — Mon, 28 Sep 2020 19:21:30 GMT

Create a table visual holding the columns of the 1st table.

Create a measure as follows:

Create a variable to hold event_date -> _edate

Create a variable to find the cycle_name in the 2nd table by filtering the 2nd table to find row where cycle_start_date < edate and cycle_end_date > edate. -> _cycleName

If _cycleName is empty return "No-cycle" otherwise return _cycleName

Re: Comparision of date in range of dates

Anonymous — Tue, 29 Sep 2020 10:12:35 GMT

[Cycle] = // calc column in Events var __date = Events[Event_Date] var __filteredForCycles = filter( Events_Summary, Events_Summary[Cycle_Start_Date] <= __date && __date <= Events_Summary[Cycle_End_Date] ) var __cycleCount = COUNTROWS( __filteredForCycles ) var __cycle = MAXX( __filteredForCycles, Events_Summary[Cycyle_Name] ) var __output = switch( TRUE(), __cycleCount = 1, __cycle, __cycleCount = 0, "No-Cycle", // If your table has mutually exclusive // cycles, this should not happen. "Many Cycles" ) return __output