Dates in Power BI

[Bomino]

Hello,
I have a table with a column named "Date Invoice Received". I would like to create a custom field "Due Date" with the the following criteria:

If “Date Invoice Received” is before the 25th of the month, then Due Date = 15th of the 2nd following month, otherwise the due date is the 15th of the 3rd following month.


I am new to Power BI and any help would be greatly appreciated.

Thanks.

 

[Chihiro]

At which stage in PowerBI are you trying to accomplish this?

My recommendation is to do it in Query Stage.

But can you clarify 2nd following month & 3rd following month?

Does it mean if invoice is received on Dec 18, 2017. Then due date is Jan 15, 2018? Or is it Feb 15, 2018?

 

[Bomino]

Chihiro,
I am trying to do this in the Modelling Stage. At first I thought about creating a measure, but I think inserting a new column will make more sense in this case.

if invoice is received on Dec 18, 2017. Then due date is Feb 15, 2018.

Thanks for your help.

 

[Chihiro]

This is needed in row context. Calculated column should be avoided in DAX when possible, especially when dealing with datetime values.

Far more stable to do this in Query Stage in M function and load the result to model. Which allows for much better error trapping etc.

I'm heading out for the night. Will see if I have time tomorrow for a sample M.

 

[Bomino]

I am new to DAX and still learning the basics. Thanks again for your help.

 

[Chihiro]

Here's how it can be done. I assumed query is from Workbook (xlsx) or from some other non-db source.

Once data is loaded, ensure "Date Invoice Received" is Date data type. Then add custom column with following "M". Note that it's case sensitive.

= if Date.Day([Date Invoice Received]) < 25 then Date.AddDays(Date.AddMonths([Date Invoice Received],2), 15 - Date.Day([Date Invoice Received])) else Date.AddDays(Date.AddMonths([Date Invoice Received],3), 25 - Date.Day([Date Invoice Received]))

Then change data type of new column to Date type. Load to model.

If it's from database, I'd just write native query statement to add the new column and have DB server do the heavy lifting.

 

[Bomino]

Chihiro,
I've extracted a sample data set and load your code to see how the DAX works. I've tried to attached a pbix file to no avail.
I have couple questions:

• I presumed you've made a typo in the 2nd part of the IF STATMENT in the "M": you had 25 instead of 15

else
Date.AddDays(Date.AddMonths([Date Invoice Received],3), 25 - Date.Day([Date Invoice Received]))

should it be:

else
Date.AddDays(Date.AddMonths([Date Invoice Received],3), 15 - Date.Day([Date Invoice Received]))

• Couple of Due Dates were not returned as 15th of the month. Please see line#60 & #61 in Picture below of the Query Editor

How can we tweak the code to have all Due Dates as 15th of the month?
I am also attaching the sample file
Again, thank you very much for your help.

 

[Chihiro]

Ah, right, I misread your initial post.

Actually if it's always going to be 15th, I'd do it in two steps to simplify formula (you can combine it into single formula if needed).
First add following custom column: for just the month calculation

= if Date.Day([Date Invoice Received]) < 25 then Date.AddMonths([Date Invoice Received],2) else Date.AddMonths([Date Invoice Received],3)

Then add 2nd column:

= #date(Date.Year([Custom]),Date.Month([Custom]),15)

After 2nd column is added. You can remove first custom column as desired.

 

[Bomino]

Thank you so much Chihiro. It works like a charm.