How does this formula Work in Power BI

[bearcub]

I have separate column for the Year, Quarter and Date. I have 3 records representing each quarter. I want to create a formula that will assignment the month associated with each quarter. The columns that i have in this table is this:

Year  Quarter Month
2012     1      1
2012     1      2
2012     1      3
2012     2      4
2012     2      5
2012     2      6
2012     3      7
2012     3      8
2012     3      9
2012     4      10
2012     4      11
2012     4      12

I added a custom column with the following formula which combined these 3 columns into this format.

1/1/2012
2/1/2012
3/1/2012
4/1/2012 ...

I used the #date function to perform this operation:

#date ([Year],([Quarter]-1)*3+[Month],1)

Question: What does the -1 *3 + [Month] do in the formula. The field headers in the formula are the column names.

I'm trying to wrap my head around how this works. Why the addition sign and Quarter -1 throws me.

What would be the equivalent formula in Excel?

Thank you for your help,

Michael

 

[gazpage]

It’s just creating a date from year, quarter and month.

The year and the day of 1, are presumably self explanatory. The month calc just uses the month and quarter, so for May quarter is 2 and month within quarter is 2. (2 - 1)*3 + 2 is 5.

However, I don’t see how this works for your data as the months are not the month of the quarter 1 - 3, but the actual month.

 

[bearcub]

The confusing bit for me was January. I missed the fact that the Quarter and the month fields are included in the same formula (calculation)

For January, it would 1-1 =0 *3 = 0 + 1 = 1. My result would be 1/1/2012.

I forgot to add the [Month] field value to the result. I think the formula works because I have month number in the Month row.

If I were doing this in Excel itself, what would the formula look like (if I were wanted to join 3 columns into 1)?

Thank you for the explanation. I was definitely missing something here.

Michael

 

[bearcub]

The confusing bit for me was January. I missed the fact that the Quarter and the month fields are included in the same formula (calculation)

For January, it would 1-1 =0 *3 = 0 + 1 = 1. My result would be 1/1/2012.

I forgot to add the [Month] field value to the result. I think the formula works because I have month number in the Month row.

If I were doing this in Excel itself, what would the formula look like (if I were wanted to join 3 columns into 1)?

Thank you for the explanation. I was definitely missing something here.

Michael