Creating Calculated Columns based on Date column and dividing by Sales column

[E5254730]

Hello,

I've formulas in 4 columns in Excel. And the similar logics (calculated columns) I need to create in PBI.

Below is the snap shot.

In Column D and F i.e. Sales Date 1 and Sales Date 2 I've added a formula as =TEXT(B2,"m/yyyy"). Were referene is B column.

In Column C (Sales in 20225), I've applied below formula in excel:

=IF(D2="1/2024",((A2/12)*0),IF(D2="2/2024",((A2/12)*1),IF(D2="3/2024",((A2/12)*2),IF(D2="4/2024",((A2/12)*3),IF(D2="5/2024",((A2/12)*4),IF(D2="6/2024",((A2/12)*5),IF(D2="7/2024",((A2/12)*6),IF(D2="7/2024",((A2/12)*6),IF(D2="8/2024",((A2/12)*7),IF(D2="9/2024",((A2/12)*8),IF(D2="10/2024",((A2/12)*9),IF(D2="11/2024",((A2/12)*10),IF(D2="12/2024",((A2/12)*11),IF(D2="1/2025",((A2/12)*12),IF(D2="2/2025",((A2/12)*11),IF(D2="3/2025",((A2/12)*10),IF(D2="4/2025",((A2/12)*9),IF(D2="5/2025",((A2/12)*8),IF(D2="6/2025",((A2/12)*7),IF(D2="7/2025",((A2/12)*6),IF(D2="8/2025",((A2/12)*5),IF(D2="9/2025",((A2/12)*4),IF(D2="10/2025",((A2/12)*3),IF(D2="11/2025",((A2/12)*2),IF(D2="12/2025",((A2/12)*1))))))))))))))))))))))))))

For in column E i.e. (Sales in 2026), below formula is applied:

=IF(F2="1/2025",((A2/12)*0),IF(F2="2/2025",((A2/12)*1),IF(F2="3/2025",((A2/12)*2),IF(F2="4/2025",((A2/12)*3),IF(F2="5/2025",((A2/12)*4),IF(F2="6/2025",((A2/12)*5),IF(F2="7/2025",((A2/12)*6),IF(F2="8/2025",((A2/12)*7),IF(F2="9/2025",((A2/12)*8),IF(F2="10/2025",((A2/12)*9),IF(F2="11/2025",((A2/12)*10),IF(F2="12/2025",((A2/12)*11),IF(F2="1/2026",((A2/12)*12),IF(F2="2/2026",((A2/12)*11),IF(F2="3/2026",((A2/12)*10),IF(F2="4/2026",((A2/12)*9),IF(F2="5/2026",((A2/12)*8),IF(F2="6/2026",((A2/12)*7),IF(F2="7/2026",((A2/12)*6),IF(F2="8/2026",((A2/12)*5),IF(F2="9/2026",((A2/12)*4),IF(F2="10/2026",((A2/12)*3),IF(F2="11/2026",((A2/12)*2),IF(F2="12/2026",((A2/12)*1)))))))))))))))))))))))))

Can someone advise on how can we apply above 2 logics in PBI report.

And whereever we see FALSE in the logics, return as zero

[bhanu_gautam · Accepted solution]

@E5254730 

Create the "Sales Date 1" and "Sales Date 2" columns:

Sales Date 1 = FORMAT('Table'[Sales Date], "m/yyyy")

Sales Date 2 = FORMAT('Table'[Sales Date], "m/yyyy")

Create the "Sales in 2025" column:

Sales in 2025 =
SWITCH(
TRUE(),
'Table'[Sales Date 1] = "1/2024", ('Table'[Annual Sales] / 12) * 0,
'Table'[Sales Date 1] = "2/2024", ('Table'[Annual Sales] / 12) * 1,
'Table'[Sales Date 1] = "3/2024", ('Table'[Annual Sales] / 12) * 2,
'Table'[Sales Date 1] = "4/2024", ('Table'[Annual Sales] / 12) * 3,
'Table'[Sales Date 1] = "5/2024", ('Table'[Annual Sales] / 12) * 4,
'Table'[Sales Date 1] = "6/2024", ('Table'[Annual Sales] / 12) * 5,
'Table'[Sales Date 1] = "7/2024", ('Table'[Annual Sales] / 12) * 6,
'Table'[Sales Date 1] = "8/2024", ('Table'[Annual Sales] / 12) * 7,
'Table'[Sales Date 1] = "9/2024", ('Table'[Annual Sales] / 12) * 8,
'Table'[Sales Date 1] = "10/2024", ('Table'[Annual Sales] / 12) * 9,
'Table'[Sales Date 1] = "11/2024", ('Table'[Annual Sales] / 12) * 10,
'Table'[Sales Date 1] = "12/2024", ('Table'[Annual Sales] / 12) * 11,
'Table'[Sales Date 1] = "1/2025", ('Table'[Annual Sales] / 12) * 12,
'Table'[Sales Date 1] = "2/2025", ('Table'[Annual Sales] / 12) * 11,
'Table'[Sales Date 1] = "3/2025", ('Table'[Annual Sales] / 12) * 10,
'Table'[Sales Date 1] = "4/2025", ('Table'[Annual Sales] / 12) * 9,
'Table'[Sales Date 1] = "5/2025", ('Table'[Annual Sales] / 12) * 8,
'Table'[Sales Date 1] = "6/2025", ('Table'[Annual Sales] / 12) * 7,
'Table'[Sales Date 1] = "7/2025", ('Table'[Annual Sales] / 12) * 6,
'Table'[Sales Date 1] = "8/2025", ('Table'[Annual Sales] / 12) * 5,
'Table'[Sales Date 1] = "9/2025", ('Table'[Annual Sales] / 12) * 4,
'Table'[Sales Date 1] = "10/2025", ('Table'[Annual Sales] / 12) * 3,
'Table'[Sales Date 1] = "11/2025", ('Table'[Annual Sales] / 12) * 2,
'Table'[Sales Date 1] = "12/2025", ('Table'[Annual Sales] / 12) * 1,
0
)

DAX
Sales in 2026 =
SWITCH(
TRUE(),
'Table'[Sales Date 2] = "1/2025", ('Table'[Annual Sales] / 12) * 0,
'Table'[Sales Date 2] = "2/2025", ('Table'[Annual Sales] / 12) * 1,
'Table'[Sales Date 2] = "3/2025", ('Table'[Annual Sales] / 12) * 2,
'Table'[Sales Date 2] = "4/2025", ('Table'[Annual Sales] / 12) * 3,
'Table'[Sales Date 2] = "5/2025", ('Table'[Annual Sales] / 12) * 4,
'Table'[Sales Date 2] = "6/2025", ('Table'[Annual Sales] / 12) * 5,
'Table'[Sales Date 2] = "7/2025", ('Table'[Annual Sales] / 12) * 6,
'Table'[Sales Date 2] = "8/2025", ('Table'[Annual Sales] / 12) * 7,
'Table'[Sales Date 2] = "9/2025", ('Table'[Annual Sales] / 12) * 8,
'Table'[Sales Date 2] = "10/2025", ('Table'[Annual Sales] / 12) * 9,
'Table'[Sales Date 2] = "11/2025", ('Table'[Annual Sales] / 12) * 10,
'Table'[Sales Date 2] = "12/2025", ('Table'[Annual Sales] / 12) * 11,
'Table'[Sales Date 2] = "1/2026", ('Table'[Annual Sales] / 12) * 12,
'Table'[Sales Date 2] = "2/2026", ('Table'[Annual Sales] / 12) * 11,
'Table'[Sales Date 2] = "3/2026", ('Table'[Annual Sales] / 12) * 10,
'Table'[Sales Date 2] = "4/2026", ('Table'[Annual Sales] / 12) * 9,
'Table'[Sales Date 2] = "5/2026", ('Table'[Annual Sales] / 12) * 8,
'Table'[Sales Date 2] = "6/2026", ('Table'[Annual Sales] / 12) * 7,
'Table'[Sales Date 2] = "7/2026", ('Table'[Annual Sales] / 12) * 6,
'Table'[Sales Date 2] = "8/2026", ('Table'[Annual Sales] / 12) * 5,
'Table'[Sales Date 2] = "9/2026", ('Table'[Annual Sales] / 12) * 4,
'Table'[Sales Date 2] = "10/2026", ('Table'[Annual Sales] / 12) * 3,
'Table'[Sales Date 2] = "11/2026", ('Table'[Annual Sales] / 12) * 2,
'Table'[Sales Date 2] = "12/2026", ('Table'[Annual Sales] / 12) * 1,
0
)

[E5254730]

@rajendraongole1 @bhanu_gautam 

Thanks! Rajendra and Gautam. I was able to fix the issue.

Both the logics are working as expected.

Thanks! once again.

[E5254730]

@rajendraongole1  @bhanu_gautam 

Sales Date 1 and @2 column is returning below result.

Hence Sales column is showing all zero values

[rajendraongole1 · Accepted solution]

Hi @E5254730  - you can start creating calculated columns for columns as

Sales Date 1 = FORMAT('Financials'[Sales Date], "m/yyyy")

Sales Date 2 = FORMAT('Financials'[Sales Date 2], "m/yyyy")

Sales in 2025 =

VAR MonthlySales = 'financials'[COGS] / 12

VAR Period = 'financials'[Sales Date 1]

VAR Multiplier =

    SWITCH(

        Period,

        "1/2024", 0,

        "2/2024", 1,

        "3/2024", 2,

        "4/2024", 3,

        "5/2024", 4,

        "6/2024", 5,

        "7/2024", 6,

        "8/2024", 7,

        "9/2024", 8,

        "10/2024", 9,

        "11/2024", 10,

        "12/2024", 11,

        "1/2025", 12,

        "2/2025", 11,

        "3/2025", 10,

        "4/2025", 9,

        "5/2025", 8,

        "6/2025", 7,

        "7/2025", 6,

        "8/2025", 5,

        "9/2025", 4,

        "10/2025", 3,

        "11/2025", 2,

        "12/2025", 1,

        0  -- default value if no match

    )

RETURN MonthlySales * Multiplier

replace with your table name instead of Financials. 

similarly create it for Sales in 2026 with sales date 2

Hope this works.

[bhanu_gautam · Accepted solution]

@E5254730 

Create the "Sales Date 1" and "Sales Date 2" columns:

Sales Date 1 = FORMAT('Table'[Sales Date], "m/yyyy")

Sales Date 2 = FORMAT('Table'[Sales Date], "m/yyyy")

Create the "Sales in 2025" column:

Sales in 2025 =
SWITCH(
TRUE(),
'Table'[Sales Date 1] = "1/2024", ('Table'[Annual Sales] / 12) * 0,
'Table'[Sales Date 1] = "2/2024", ('Table'[Annual Sales] / 12) * 1,
'Table'[Sales Date 1] = "3/2024", ('Table'[Annual Sales] / 12) * 2,
'Table'[Sales Date 1] = "4/2024", ('Table'[Annual Sales] / 12) * 3,
'Table'[Sales Date 1] = "5/2024", ('Table'[Annual Sales] / 12) * 4,
'Table'[Sales Date 1] = "6/2024", ('Table'[Annual Sales] / 12) * 5,
'Table'[Sales Date 1] = "7/2024", ('Table'[Annual Sales] / 12) * 6,
'Table'[Sales Date 1] = "8/2024", ('Table'[Annual Sales] / 12) * 7,
'Table'[Sales Date 1] = "9/2024", ('Table'[Annual Sales] / 12) * 8,
'Table'[Sales Date 1] = "10/2024", ('Table'[Annual Sales] / 12) * 9,
'Table'[Sales Date 1] = "11/2024", ('Table'[Annual Sales] / 12) * 10,
'Table'[Sales Date 1] = "12/2024", ('Table'[Annual Sales] / 12) * 11,
'Table'[Sales Date 1] = "1/2025", ('Table'[Annual Sales] / 12) * 12,
'Table'[Sales Date 1] = "2/2025", ('Table'[Annual Sales] / 12) * 11,
'Table'[Sales Date 1] = "3/2025", ('Table'[Annual Sales] / 12) * 10,
'Table'[Sales Date 1] = "4/2025", ('Table'[Annual Sales] / 12) * 9,
'Table'[Sales Date 1] = "5/2025", ('Table'[Annual Sales] / 12) * 8,
'Table'[Sales Date 1] = "6/2025", ('Table'[Annual Sales] / 12) * 7,
'Table'[Sales Date 1] = "7/2025", ('Table'[Annual Sales] / 12) * 6,
'Table'[Sales Date 1] = "8/2025", ('Table'[Annual Sales] / 12) * 5,
'Table'[Sales Date 1] = "9/2025", ('Table'[Annual Sales] / 12) * 4,
'Table'[Sales Date 1] = "10/2025", ('Table'[Annual Sales] / 12) * 3,
'Table'[Sales Date 1] = "11/2025", ('Table'[Annual Sales] / 12) * 2,
'Table'[Sales Date 1] = "12/2025", ('Table'[Annual Sales] / 12) * 1,
0
)

DAX
Sales in 2026 =
SWITCH(
TRUE(),
'Table'[Sales Date 2] = "1/2025", ('Table'[Annual Sales] / 12) * 0,
'Table'[Sales Date 2] = "2/2025", ('Table'[Annual Sales] / 12) * 1,
'Table'[Sales Date 2] = "3/2025", ('Table'[Annual Sales] / 12) * 2,
'Table'[Sales Date 2] = "4/2025", ('Table'[Annual Sales] / 12) * 3,
'Table'[Sales Date 2] = "5/2025", ('Table'[Annual Sales] / 12) * 4,
'Table'[Sales Date 2] = "6/2025", ('Table'[Annual Sales] / 12) * 5,
'Table'[Sales Date 2] = "7/2025", ('Table'[Annual Sales] / 12) * 6,
'Table'[Sales Date 2] = "8/2025", ('Table'[Annual Sales] / 12) * 7,
'Table'[Sales Date 2] = "9/2025", ('Table'[Annual Sales] / 12) * 8,
'Table'[Sales Date 2] = "10/2025", ('Table'[Annual Sales] / 12) * 9,
'Table'[Sales Date 2] = "11/2025", ('Table'[Annual Sales] / 12) * 10,
'Table'[Sales Date 2] = "12/2025", ('Table'[Annual Sales] / 12) * 11,
'Table'[Sales Date 2] = "1/2026", ('Table'[Annual Sales] / 12) * 12,
'Table'[Sales Date 2] = "2/2026", ('Table'[Annual Sales] / 12) * 11,
'Table'[Sales Date 2] = "3/2026", ('Table'[Annual Sales] / 12) * 10,
'Table'[Sales Date 2] = "4/2026", ('Table'[Annual Sales] / 12) * 9,
'Table'[Sales Date 2] = "5/2026", ('Table'[Annual Sales] / 12) * 8,
'Table'[Sales Date 2] = "6/2026", ('Table'[Annual Sales] / 12) * 7,
'Table'[Sales Date 2] = "7/2026", ('Table'[Annual Sales] / 12) * 6,
'Table'[Sales Date 2] = "8/2026", ('Table'[Annual Sales] / 12) * 5,
'Table'[Sales Date 2] = "9/2026", ('Table'[Annual Sales] / 12) * 4,
'Table'[Sales Date 2] = "10/2026", ('Table'[Annual Sales] / 12) * 3,
'Table'[Sales Date 2] = "11/2026", ('Table'[Annual Sales] / 12) * 2,
'Table'[Sales Date 2] = "12/2026", ('Table'[Annual Sales] / 12) * 1,
0
)