Forum Discussion
Calculated Column to check date cycle
Hi Folks,
I have two tables Events and Events_Cycle.
The events table has a column Date of Event.
The events_Cycle table has 3 columns Cycle_name, cycle start date, and cycle end date.
I want to check the date of the event in the Events table and compare it with the Events_Cycle table. I want to create a DAX column to classify to which Event_Cycle, the event belongs to.
My data looks like:
Events Events_Cycle
My Desired Output:
Events
Many thanks in advance
Anonymous
Add the following Code as a New Column to your Events Table:Cycle = VAR __DATE = Events[Event Date] VAR __CYCLE = MAXX( FILTER( 'Events Cycle', __DATE >= 'Events Cycle'[Cycle Start] && __DATE <= 'Events Cycle'[Cycle End] ), 'Events Cycle'[Cycle] ) RETURN IF( ISBLANK(__CYCLE), "No Cycle", __CYCLE )________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
2 Replies
- Fowmy
Super User
Anonymous
Add the following Code as a New Column to your Events Table:Cycle = VAR __DATE = Events[Event Date] VAR __CYCLE = MAXX( FILTER( 'Events Cycle', __DATE >= 'Events Cycle'[Cycle Start] && __DATE <= 'Events Cycle'[Cycle End] ), 'Events Cycle'[Cycle] ) RETURN IF( ISBLANK(__CYCLE), "No Cycle", __CYCLE )________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
- lkalawski
Resident Rockstar
Hi Anonymous
Please use this measure:
Cycle = VAR _cycle = CALCULATE ( MAX ( Events_Cycle[Events_Cycle] ), FILTER ( ALL ( Events_Cycle ), MAX ( Events[Event_Date] ) >= Events_Cycle[Cycle_Start_Date] && MAX ( Events[Event_Date] ) <= Events_Cycle[Cycle_End_Date] ) ) RETURN IF ( ISBLANK ( _cycle ), "No-Cycle", _cycle )
_______________
If I helped, please accept the solution and give kudos! 😀
