Forum Discussion
Help with DAX measure for two dates
I have two date columns in my dataset, one entry and one exit. Customers can have multiple entries and exits in the same fiscal year. I need to categorize them and it seems like a measure or calculated column would be best but I'm struggling with the DAX for it. Basically, if their max entry date is later than their max exit date within the same fiscal year I would classify them as a 1 or as reactivated, but if their max exit date is later than their max entry date within the same fiscal year I would classify them as a 0 or as withdrawn.
I don't know how your date logic is set up (like whether you have a date dimension table or not), so ignoring the year aspect for now, you could write something like this:
VAR _MaxEnter = CALCULATE ( MAX ( Table1[Entry] ), ALLEXCEPT ( Table1, Table1[Customer] ) ) VAR _MaxExit = CALCULATE ( MAX ( Table1[Entry] ), ALLEXCEPT ( Table1, Table1[Customer] ) ) RETURN SWITCH ( TRUE (), ISBLANK ( _MaxExit ) && ISBLANK ( _MaxEnter ), 0, ISBLANK ( _MaxExit ), 1, ISBLANK ( _MaxEnter ), 0, _MaxExit > _MaxEnter, 0, _MaxExit <= _MaxEnter, 1 )Please double-check my logic for how you want the various cases to work.
6 Replies
- AlexisOlson
Super User
I don't know how your date logic is set up (like whether you have a date dimension table or not), so ignoring the year aspect for now, you could write something like this:
VAR _MaxEnter = CALCULATE ( MAX ( Table1[Entry] ), ALLEXCEPT ( Table1, Table1[Customer] ) ) VAR _MaxExit = CALCULATE ( MAX ( Table1[Entry] ), ALLEXCEPT ( Table1, Table1[Customer] ) ) RETURN SWITCH ( TRUE (), ISBLANK ( _MaxExit ) && ISBLANK ( _MaxEnter ), 0, ISBLANK ( _MaxExit ), 1, ISBLANK ( _MaxEnter ), 0, _MaxExit > _MaxEnter, 0, _MaxExit <= _MaxEnter, 1 )Please double-check my logic for how you want the various cases to work.
I have a date table that has an active relationship between the date and the entry date column and an inactive reltionship with the exit date. The entry/exit table also has a column for fiscal year since it runs outside of a calendar year.
- AlexisOlson
Do you ever have rows where the entry and exit dates in that specific row are from different fiscal years? If so, which date does the fiscal year column correspond to?
No, every row only contains dates for the corresponding fiscal year.
Hi CoordAnalytics ,
Could you tell me if your problem has been solved by AlexisOlson 's suggestion? If it is, kindly Accept it as the solution. More people will benefit from it.
Or if you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.
How to provide sample data in the Power BI Forum - Microsoft Power BI Community
How to Get Your Question Answered Quickly - Microsoft Power BI Community
Best Regards,
Eyelyn Qin
