Calculated Column to check date cycle

[Anonymous]

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

@amitchandak 

Many thanks in advance

[Fowmy · Accepted solution]

@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 🙂

YouTube  LinkedIn

[lkalawski]

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! 😀