Compare Date Column with current date

[Pete230]

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

[JavidM · Accepted 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!

[Pete230]

Works perfectly fine, thanks a lot!

Cheers
Pete

[JavidM]

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.

[Pete230]

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!

[JavidM]

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:

New Column =

IF('Table'[Date].[Date] > EOMONTH(TODAY(), 0), 0, 'Table'[Amount])

Let me know if it worked! Thanks!