Forum Discussion

jdubs's avatar
jdubs
Helper V
3 years ago

Problem using 'SWITCH' function

I'm trying to convert an expression from an old SSRS report to Power BI and running into the following problem. I have a table of Monthly sales figures (Jan, Feb, Mar,...) and the rows contain numeri...
Need Help
lbendlin's avatar
lbendlin
Super User
3 years ago

There is another option

 

Monthly Sales = 
DIVIDE(
switch(Month(Now(),1, Sum(Jan_Value),
2, Sum(Feb_Value),
3, Sum(Mar_Value),
4, Sum(Apr_Value),
5, Sum(May_Value),
6, Sum(Jun_Value),
7, Sum(Jul_Value),
8, Sum(Aug_Value),
9, Sum(Sept_Value),
10, Sum(Oct_Value),
11, Sum(Nov_Value),
12, Sum(Dec_Value))
,1000)

 

Having values per month in different column is a design red flag.

  • jdubs's avatar
    jdubs
    Helper V
    3 years ago

    I was able to get it to work by adding TRUE() before the arguments, so:

     

    Monthly Sales = 

    (switch(

    TRUE(),

    Month(Now()) = 1, Sum(Jan_Value),
    Month(Now()) = 2, Sum(Feb_Value),
    Month(Now()) = 3, Sum(Mar_Value),
    Month(Now()) = 4, Sum(Apr_Value),
    Month(Now()) = 5, Sum(May_Value),
    Month(Now()) = 6, Sum(Jun_Value),
    Month(Now()) = 7, Sum(Jul_Value),
    Month(Now()) = 8, Sum(Aug_Value),
    Month(Now()) = 9, Sum(Sept_Value),
    Month(Now()) = 10, Sum(Oct_Value),
    Month(Now()) = 11, Sum(Nov_Value),
    Month(Now()) = 12, Sum(Dec_Value),

    BLANK()

    )

     

    I could also drop the /1000 since I can just format the column directly in POWER BI.

     

    Agreed on the data structure, but this was easier than trying to get a programmer to update the source data.