Forum Discussion
Solved
Creating Calculated Columns based on Date column and dividing by Sales column
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
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
)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] / 12VAR 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 * Multiplierreplace with your table name instead of Financials.similarly create it for Sales in 2026 with sales date 2Hope this works.
4 Replies
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] / 12VAR 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 * Multiplierreplace with your table name instead of Financials.similarly create it for Sales in 2026 with sales date 2Hope this works.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
)Sales Date 1 and @2 column is returning below result.
Hence Sales column is showing all zero values
Thanks! Rajendra and Gautam. I was able to fix the issue.
Both the logics are working as expected.
Thanks! once again.
