Forum Discussion

CaraJonesReadle's avatar
CaraJonesReadle
Frequent Visitor
6 years ago
Solved

IF stateemnt based on dates

Hello

 

I want to add a custom column that will give me a specific date column based on whether a sperate date column is at the begining or end of each month.

 

Currently I have the folllowing Custom Column: 

if Text.Contains([#"Explanation -Remark-"], "Accrual") then [Last End of Week] else [End of Week])

 

I now need to expand it slightly so that the end of month accrual (occurs on the last day of the month) gets the Last End of Week date and the reversal (which occures on the 1st day of the month) gets the End of Week Date.

 

Thanks for your help

Cara

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi CaraJonesReadle ,

    You can try to use the following if statement to replace your condition formula in the custom column: (I add 'code indentation style' to highlight the different levels of nested if statements)

    AddColumn=Table.AddColumn(Source,"Custom",each 
	if 
		Text.Contains([#"Explanation -Remark-"], "Accrual") 
	then 
		if 
			[Date]=Date.EndOfMonth([Date]) or Date.Day([Date])=1 
		then 
			if Date.Day([Date])=1 then [End of Week] else [Last End of Week] 
		else 
			[Last End of Week] 
	else 
		[End of Week]
	)

    Regards,

    Xiaoxin Sheng

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi CaraJonesReadle ,

    You can try to use the following if statement to replace your condition formula in the custom column: (I add 'code indentation style' to highlight the different levels of nested if statements)

    AddColumn=Table.AddColumn(Source,"Custom",each 
	if 
		Text.Contains([#"Explanation -Remark-"], "Accrual") 
	then 
		if 
			[Date]=Date.EndOfMonth([Date]) or Date.Day([Date])=1 
		then 
			if Date.Day([Date])=1 then [End of Week] else [Last End of Week] 
		else 
			[Last End of Week] 
	else 
		[End of Week]
	)

    Regards,

    Xiaoxin Sheng