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,
Want some help with creating a calculated column (not a measure). To explain I have recreated dummy data below. I have a column of data displaying the centre and a column displaying a submission month. What I'm looking for is a way to create the third column below. I want it to do is determine if the submission month is the latest submission (or most recent) by centre. So in the example below Perth has no Septmber 2017 submission so the latest submission for Perth is August 2017.
Centre | Submission Month | Submission |
Sydney | May-17 | Previous Submission |
Brisbane | Jun-17 | Previous Submission |
Perth | Jul-17 | Previous Submission |
Darwin | Aug-17 | Previous Submission |
Melbourne | Sep-17 | Previous Submission |
Sydney | Jun-17 | Previous Submission |
Brisbane | Jun-17 | Previous Submission |
Perth | Jun-17 | Previous Submission |
Darwin | Jun-17 | Previous Submission |
Melbourne | Jun-17 | Previous Submission |
Sydney | Jul-17 | Previous Submission |
Brisbane | Jul-17 | Previous Submission |
Perth | Jul-17 | Previous Submission |
Darwin | Jul-17 | Previous Submission |
Melbourne | Jul-17 | Previous Submission |
Sydney | Aug-17 | Previous Submission |
Brisbane | Aug-17 | Previous Submission |
Perth | Aug-17 | Latest Submission |
Darwin | Aug-17 | Previous Submission |
Melbourne | Aug-17 | Previous Submission |
Sydney | Sep-17 | Latest Submission |
Brisbane | Sep-17 | Latest Submission |
Darwin | Sep-17 | Latest Submission |
Melbourne | Sep-17 | Latest Submission |
Solved! Go to Solution.
Hi,
Try this calculated column formula
=if(CALCULATE(MAX([Submission Month]),FILTER(Data,Data[Centre]=EARLIER(Data[Centre])))=[Submission Month],"Latest submission","Previous month")
Please note that the answer in the 5th row will also be Latest submission becausee the date is Sep-17.
Hope this helps.
Hi,
Try this calculated column formula
=if(CALCULATE(MAX([Submission Month]),FILTER(Data,Data[Centre]=EARLIER(Data[Centre])))=[Submission Month],"Latest submission","Previous month")
Please note that the answer in the 5th row will also be Latest submission becausee the date is Sep-17.
Hope this helps.
this is not working now..... I now have data for October 2017 in the and September 2017 is showing as the latest month and October is labelled as previous month
Hi,
Share the link from where i can download your file. Also, point out the mistake in the result which you get by my formula.
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 |
---|---|
110 | |
97 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |