different condition applied on a single column for percentage calculation in power bi

[NISHA_S]

hi,

here i have this data...

risk_date is date data type...

I want to create  a 3 new column based on below conditions...

1)Capcity 1 column 

if  a risk  date on or preceding 12/31/2020:  means value of column should be 100% of share_value

if  a risk  date from 1/1/2021 – 5/16/2021: means value of column should be 29.5% of share_value

if  a risk  date 5/17/2021 onwards, the following rules will apply:

1. If column Utilized equals “N”, value of column should be 29.5% of share_value
2. If column Utilized equals equals “Y”, value of column will be 19.67%

2)2nd column 

if  a risk  date from 1/1/2021 – 5/16/2021: value of column will be 70.5% of share_value

if  a risk  date 5/17/2021 onwards, the following rules will apply:

            1.If column Utilized equals “N”, value of column should be, 70.5% of share_value

             2.If column Utilized equals equals “Y”, value of column will be 47%

3)3rd column 

if  a risk  date 5/17/2021 onwards, the following rules will apply:

          1.If column Utilized equals “N”, value of column will be 0% of share_value

          2.If column Utilized equals equals “Y”, value of column will be 33.33%

[TomMartens]

Hey @NISHA_S ,

I used DAX to create a Calculated Column for the 1st column you need. Here is the DAX, be aware that you have to replace the tablename 'yourtable' to your needs

Capacity 1 column = 
var __riskdate = 'yourtable'[risk_date]
var __sharevalue = 'yourtable'[share_value]
var __utilization = 'yourtable'[utilized]
return
SWITCH( 
    TRUE()
    , __riskdate <= DATE(2020 , 12 , 31) ,  __sharevalue
    ,  __riskdate >= DATE(2021 , 1 , 1) && __riskdate <= DATE(2021 , 5 , 16) , __sharevalue * 0.295
    , __riskdate >= DATE(2021 , 5 , 17) && __utilization = "N" , __sharevalue * 0.295
    , __riskdate >= DATE(2021 , 5 , 17) && __utilization = "Y" , __sharevalue * 0.1967
)

The DAX statement for the other 2 calculated columns is similar and the DAX can easily be adapted to your needs.
This article explains how to get started with calculated columns: Tutorial: Create calculated columns in Power BI Desktop - Power BI | Microsoft Docs
This article explains the DAX functions SWITCH(): SWITCH – DAX Guide

If you need more help, create a pbix file that contains sample data but still reflects your data model, upload the file to onedrive or dropbox and share the link. If you are using Excel to create the sample data, share the xlsx as well.

Hopefully, this is what you are looking for.

Regards,

Tom

[amitchandak · Accepted solution]

@NISHA_S , Try new columns like

col1 =
Switch(True() ,
[risk_date] <=date(2020,12,31) , [share_value],
[risk_date] <=date(2021,05,16) , [share_value]*.295,
[Utilized] = "N" ,[share_value]*.295 , // we only > date(2021,05,16)
[share_value]* .1967
)

col2 =
Switch(True() ,
[risk_date] > date(2020,12,31) && [risk_date] <=date(2021,05,16) , [share_value]*.705,
[risk_date] >date(2021,05,16) && [Utilized] = "N" ,[share_value]*.705,
[risk_date] >date(2021,05,16) && [Utilized] = "Y" ,[share_value]*.47
)

col3 =
Switch(True() ,
[risk_date] >date(2021,05,16) && [Utilized] = "N" ,[share_value]*0,
[risk_date] >date(2021,05,16) && [Utilized] = "Y" ,[share_value]*.3333
)