IF statement for Month to equal a specified %

[Anonymous]

Hello,

Here's my scenario. I have a table with a Month column showing January 2019, Feburary 2019, etc... I would like to write an If statement (or if you have something else in mind thats more effective) that shows if row has January then display 10.5%, if row has February then display 9%, if row has Month then display the specified %. Also I will be using the output for another calculation.. Thank you. 

[lbeneyze]

Good morning wjtsk1,

Personnally, I prefer to solve this via Power Query. 

I suggest you the following approach:

  - create a Date percentage table by entering data:
       Column 1: Month with "January 2019", "February 2019"

       Column 2: Percentage with 10.5%

  - use a merge query in Power Query to "Merge" column percentage based on column Date in your manual table with your transaction query. https://www.youtube.com/watch?v=qXH4WjCykLc

Did I answer your question, please mark my post as a solution

If you liked my solution, please give it a thumbs up.

Thank you and keep up the good job!

Kind regards,

Lohic Beneyzet

[v-yiruan-msft]

Hi @Anonymous，

You can create one measure just like the one in the below screenshot(assume that you have the similar Month column with text data type):

Note: Replace the numerical part of the formula with the actual percentage

Measure =

SWITCH (

    LEFT ( MAX ( 'Months'[Month] ), LEN ( MAX ( 'Months'[Month] ) ) - 5 ),

    "January", 0.105,

    "February", 0.09,

    "March", 0.3,

    "April", 0.024,

    "May", 0.035,

    "June", 0.21,

    "July", 0.09,

    "August", 0.37,

    "September", 0.08,

    "October", 0.38,

    "November", 0.49,

    "December", 0.175

)

If the above measure formula is not applicable in your scenario, please provide the related table structure and sample data.

Best Regards

Rena

[Anonymous]

Hi @v-yiruan-msft,

I tried this way (

SWITCH

(LEFT(MAX('Shrinkage (Teleopti)'[** Month]),

LEN( MAX('Shrinkage (Teleopti)'[** Month])) - 5)

but received the following result:

It's not accepting my screen shot for some reason.. for every month its giving me a result of 100%

I believe my date column is the same as yours:

[v-yiruan-msft]

Hi @Anonymous ,

If the field "Shrinkage (Teleopti)'[** Month]" is Text data type, it can get the correct result using my provided formula. Could you please provide your sample PBIX file if it is convenient? Then I can check where the problem is .

Best Regards

Rena

[Anonymous]

@v-yiruan-msft 

Sorry for the late response. I had some other items come up..

I have tried the month column with the format being date as well as text.

Would the other fields make a difference if I'm only using the month column and the formula you provided? The screenshot provided is literally all I'm using for this formula and it is in text format.  I did have a typo at the end but now it's not giving me any result. It's just blank.  I'm trying to view the result in its own table and page. 

[v-yiruan-msft]

Hi @Anonymous ,

Could you please provide me your PBIX file(exclude the sensitive data) if it is convenient? Then I can check the cause of problem based on your provided file. Thank you.

Best Regards

Rena

[edhans]

Use the SWITCH function. Below is some pseudocode:

My Measure =
SWITCH (
    TRUE (),
    Table[Month] = "January", .01,
    Table[Month] = "February", .02
)

Just keep adding the months and values as needed. Much easier to read and edit than 12 nested IF functions.

[Anonymous]

Thank you @edhans . I was able to complete this using a new column but not a measure.