Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi, not sure if this can be done in calculated column. or a measure. Confused trying to figure it out.
I have survey data with completion dates for multiple months. I want to label (in a calculated column) the max date and previous date for each month. If it is not the max date, label as previous.
Name | Completion date | Maxdate |
Canada | 22/03/2021 4:31:41 PM | Max |
Canada | 21/03/2021 9:04:09 AM | Previous |
USA | 23/03/2021 9:05:48 AM | Max |
USA | 22/03/2021 12:07:39 PM | Previous |
Canada | 15/02/2021 4:31:41 PM | Max |
Canada | 14/02/2021 9:04:09 AM | Previous |
USA | 16/02/2021 9:05:48 AM | Max |
USA | 14/02/2021 12:07:39 PM | Previous |
Essentially, i'm looking to represent my data (tied to those max/previous dates) as a clustered chart for those different monthly submissions.
Thanks if any help can be had.
Solved! Go to Solution.
Hello @mcinnisbr ,
You can try this code for your calculated column:
Maxdate =
var currentName = 'Table'[Name]
var currentMonth = MONTH('Table'[CompletionDate])
var currentDate = 'Table'[CompletionDate]
var maxDate =
CALCULATE(
MAX('Table'[CompletionDate]),
FILTER('Table',
'Table'[Name] = currentName &&
MONTH('Table'[CompletionDate]) = currentMonth)
)
return IF('Table'[CompletionDate] = maxDate, "max", "previous")
Did I answer your question? Mark my post as a solution!
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.
Check out my latest demo report in the data story gallery.
Stand with Ukraine!
Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/
Thank you!
Hello @mcinnisbr,
Can you tell me if you built this graphic on Power BI??? If so, could you let me know how?
Hello @mcinnisbr ,
You can try this code for your calculated column:
Maxdate =
var currentName = 'Table'[Name]
var currentMonth = MONTH('Table'[CompletionDate])
var currentDate = 'Table'[CompletionDate]
var maxDate =
CALCULATE(
MAX('Table'[CompletionDate]),
FILTER('Table',
'Table'[Name] = currentName &&
MONTH('Table'[CompletionDate]) = currentMonth)
)
return IF('Table'[CompletionDate] = maxDate, "max", "previous")
Did I answer your question? Mark my post as a solution!
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.
Check out my latest demo report in the data story gallery.
Stand with Ukraine!
Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/
Thank you!
I understand this. I'm getting an error (or the column measure error) when attempting to add the .[Year] and .[MonthNo]. I don't understand how to add this part? Do i have to attach a date table column to this?