Forum Discussion

Pete230's avatar
Pete230
Helper III
3 years ago
Solved

Compare Date Column with current date

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 numb...
Need Help
  • JavidM's avatar
    JavidM
    3 years ago

    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!

JavidM's avatar
JavidM
New Member
3 years ago

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!