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 guys,
I'm struggeling with the following: I would like to compare a date colum (last day of month) with the current month, if the current month is less or equal as the date colum then the numbers of the next column should be displayed, otherwise there should be a 0.
So I would like to see all the numbers until mai and for the following months just a 0.
I guess it's not to hard, but I can not figure it out right now, any ideas will be appreciated.
Thank you and regards
Pete
Solved! Go to Solution.
Alternatively, you can use the following M code without converting the Date column into datetime format:
= Table.AddColumn(#"Changed Type", "Custom", each if [Date] > DateTime.Date(Date.EndOfMonth(DateTime.LocalNow())) then 0 else [Amount])
Hope this helps!
Alternatively, you can use the following M code without converting the Date column into datetime format:
= Table.AddColumn(#"Changed Type", "Custom", each if [Date] > DateTime.Date(Date.EndOfMonth(DateTime.LocalNow())) then 0 else [Amount])
Hope this helps!
Works perfectly fine, thanks a lot!
Cheers
Pete
You can use the following M code:
= Table.AddColumn(#"Changed Type1", "Custom", each if [Date] > Date.EndOfMonth(DateTime.LocalNow()) then 0 else [Amount])
Bear in mind that you have to convert the date column to datetime format before applying the function.
Thank you for your answer. I would like to create the same in M (Power Querry) not in DAX, I wasnt pricise on that. Do you know that as well?
Thank you!
Hi there,
If I understood your question correctly, you want to show the amounts up until the current month (May 2023) leaving zeros for all consecutive months. So, when we are in June 2023, the data will appear for the respective month only leaving July and future months as 0.
You can try using the following DAX function:
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 |
---|---|
107 | |
97 | |
75 | |
65 | |
53 |
User | Count |
---|---|
144 | |
103 | |
98 | |
85 | |
64 |