Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
Thank you in advance!
Solved! Go to Solution.
I think it might be even easier to add an index in Power Query, with 0 being the earliest day.
Then just crate two measures.
One for the latest and one for the second latest
Let me know if it works 🙂
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
I think it might be even easier to add an index in Power Query, with 0 being the earliest day.
Then just crate two measures.
One for the latest and one for the second latest
Let me know if it works 🙂
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
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
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
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.
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
123 | |
101 | |
71 | |
61 |