Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi Experts,
Hello to Everyone. I am a new POWER BI user, but Heavy user of Tableau. I have the following Three Columns of Data in my Table.
ID | Status | Changed Date |
2303 | A | 11/7/2017 14:00 |
2303 | A | 11/7/2017 14:01 |
2303 | A | 11/7/2017 14:01 |
2303 | A | 11/7/2017 14:02 |
4657 | B | 11/6/2017 14:53 |
4657 | B | 11/6/2017 14:53 |
4657 | B | 11/8/2017 10:51 |
4988 | C | 11/8/2017 11:18 |
4988 | C | 11/9/2017 11:05 |
4988 | C | 11/9/2017 11:08 |
4988 | C | 11/9/2017 11:10 |
Using the Following LOD Expression in Tableau
{FIXED [ID],[Status]:MAX([Changed Date])}
Which Simply Says that for a Particular ID and Status Find out the Maximum Date from Many Changed Dates Available for that Combination of ID and Status.
So When I Use the above Formula, I get MAX Date in additional Column
ID | Status | Changed Date | MAX Date |
2303 | A | 11/7/2017 14:00 | 11/7/2017 14:02 |
2303 | A | 11/7/2017 14:01 | 11/7/2017 14:02 |
2303 | A | 11/7/2017 14:01 | 11/7/2017 14:02 |
2303 | A | 11/7/2017 14:02 | 11/7/2017 14:02 |
4657 | B | 11/6/2017 14:53 | 11/8/2017 10:51 |
4657 | B | 11/6/2017 14:53 | 11/8/2017 10:51 |
4657 | B | 11/8/2017 10:51 | 11/8/2017 10:51 |
4988 | C | 11/8/2017 11:18 | 11/9/2017 11:10 |
4988 | C | 11/9/2017 11:05 | 11/9/2017 11:10 |
4988 | C | 11/9/2017 11:08 | 11/9/2017 11:10 |
4988 | C | 11/9/2017 11:10 | 11/9/2017 11:10 |
So for all the Dates in 3rd Column , I get Only 3 Dates for a particular ID and its Corresponding Status which are the MAXIMUM dates I Need
How Can I Get the 4th Column (MAX DATE) in POWER BI with some Formula. Can somebody provide the FORMULA?
Thanks a Lot
Deepak
Solved! Go to Solution.
Hi, you can usea a calculated column:
MaxDate = CALCULATE ( MAX ( Table1[Changed Date] ), FILTER ( Table1, Table1[ID] = EARLIER ( Table1[ID] ) && Table1[Status] = EARLIER ( Table1[Status] ) ) )
Regards
Victor
Lima - Peru
Hi, you can usea a calculated column:
MaxDate = CALCULATE ( MAX ( Table1[Changed Date] ), FILTER ( Table1, Table1[ID] = EARLIER ( Table1[ID] ) && Table1[Status] = EARLIER ( Table1[Status] ) ) )
Regards
Victor
Lima - Peru
Victor, Thanks Man!!, This is what I was looking for.
Thanks
Deepak
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |