Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

bharathkarre_0-1601319649626.png

 My Desired Output:

 

Events

bharathkarre_1-1601319739545.png

@amitchandak 

 

Many thanks in advance

 

1 ACCEPTED SOLUTION
Fowmy
Super User
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 )

Fowmy_0-1601321903053.png

________________________

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

 



 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

2 REPLIES 2
Fowmy
Super User
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 )

Fowmy_0-1601321903053.png

________________________

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

 



 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

lkalawski
Memorable Member
Memorable Member

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 )

lkalawski_0-1601320708931.png

 


_______________
If I helped, please accept the solution and give kudos! 😀

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.