Forum Discussion

E5254730's avatar
E5254730
Helper II
1 year ago
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 ...
Need Help
  • bhanu_gautam's avatar
    bhanu_gautam
    1 year ago

    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
    )
  • rajendraongole1's avatar
    rajendraongole1
    1 year ago

    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.