topic Re: Help with DAX measure for two dates in DAX Commands and Tips

Help with DAX measure for two dates

CoordAnalytics — Mon, 07 Feb 2022 19:29:25 GMT

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.

Re: Help with DAX measure for two dates

AlexisOlson — Mon, 07 Feb 2022 21:53:39 GMT

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.

Re: Help with DAX measure for two dates

CoordAnalytics — Mon, 07 Feb 2022 22:00:15 GMT

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.

Re: Help with DAX measure for two dates

AlexisOlson — Mon, 07 Feb 2022 22:08:36 GMT

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?

Re: Help with DAX measure for two dates

CoordAnalytics — Mon, 07 Feb 2022 22:11:05 GMT

No, every row only contains dates for the corresponding fiscal year.

Re: Help with DAX measure for two dates

AlexisOlson — Tue, 08 Feb 2022 15:40:21 GMT

Then maybe something like this will work for the variables:

VAR _MaxEnter = CALCULATE ( MAX ( Table1[Entry Date] ), ALLEXCEPT ( Table1, Table1[Customer], Table1[FiscalYear] ) ) VAR _MaxExit = CALCULATE ( MAX ( Table1[Exit Date] ), ALLEXCEPT ( Table1, Table1[Customer], Table1[FiscalYear] ) )

It's hard to say for sure though without seeing your actual data.

Re: Help with DAX measure for two dates

Anonymous — Mon, 14 Feb 2022 02:15:01 GMT

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