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

How to calculate if the last date is the same with 2nd to the last date

Hi, Community.

 

I have this DAX formula to compute for the last date and 2nd to the last date. But I have this scenario that last date is the same with 2nd to the last date. I am getting a result of 8 (November 12 and November 24), wherein it should be 0, because the last date is Nov 24 and 2nd or next date is also Nov 24. Can you help what should I add or edit in the dax formula?

katerinepr_0-1654121221174.png

 

Thank you in advance!

1 ACCEPTED SOLUTION
NickolajJessen
Solution Sage
Solution Sage

I think it might be even easier to add an index in Power Query, with 0 being the earliest day.

NickolajJessen_0-1654154054410.png

Then just crate two measures.
One for the latest and one for  the second latest

NickolajJessen_1-1654154764981.png
Let me know if it works 🙂 

 

View solution in original post

8 REPLIES 8
v-shex-msft
Community Support
Community Support

HI @Anonymous,

I think you can consider using summarize function to aggregate your records. You can add a variable to use summarise function with 'change number' and date fields as category group, then add a custom field with the count of the current date. 

After these steps, you can use the count field to get the latest unique date value, and you can use the current 'change number' and the latest date value to lookup the original table to get the 'duration' field values.
Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
NickolajJessen
Solution Sage
Solution Sage

I think it might be even easier to add an index in Power Query, with 0 being the earliest day.

NickolajJessen_0-1654154054410.png

Then just crate two measures.
One for the latest and one for  the second latest

NickolajJessen_1-1654154764981.png
Let me know if it works 🙂 

 

selimovd
Super User
Super User

Hey @Anonymous ,

 

it would help if you show at least the result of the calculated column and what you would expect.

It would even be better if you could give an sample file with the logic that doesn't work. With only half a screenshot and without further information it's very hard to figure out what you want to archive.

 

Best regards

Denis

 

Anonymous
Not applicable

Hi, @selimovd . I edited the screenshot and what result I wanted. I want to my _2ndtothelastdate return the 2nd date from the last date. 

Hey @Anonymous ,

 

you get the sum of IsWorkingDay-1 in the given time frame. This seems to be 8.

What exactly is your question?

 

Half of the tables of the measure are missing, so I can just guess what is happening.

 

Best regards

Denis

Anonymous
Not applicable

Actually, the DAX is working. However, I need to get the date difference between the latest date and the previous date. Latest date/End date = sign-off date of change analyst - manila, then get the date difference to whatever date comes before that date. There are 4 dates in the column, Nov 24 is the sign off date of change analyst manila, subtract to Nov 24 also, because that is the 2nd to the last date. But since they are the same, that's why my formula is getting the Nov 12. I hope I explained it well. 

Hi,

 

Right now you are using the MAX Date to determine, which one is the latest, which causing an issue when the 2nd date is the same as the 1st.

You might also consider using RANK to add an index to your entries. In that way, you reference MIN(index) as your 1st entry (latest) and MIN(index)+1 as you 2nd latest entry

Anonymous
Not applicable

Can you help me with the DAX? Awhile ago I was trying the RANKX and also add RANK column, but I'm only getting either blank or error. 

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.