Power BI calculated column [With 71 Useful Examples]

In this Power BI Tutorial, we will discuss the calculated column in Power BI. And also we will see how to create a calculated column with different examples of Power BI calculated columns:

  • Power bi calculated column
  • Power bi calculated column vs measure
  • Power bi calculated column if
  • Power bi calculated column aggregate
  • Power bi calculated column average
  • Power bi calculated column average by category
  • Power bi calculated column add two columns
  • Power bi calculated column another table
  • Power bi calculated column if and
  • Power bi calculated column moving average
  • Power bi calculated column cumulative sum
  • Power bi calculated column count rows
  • Power bi calculated column count if
  • Power bi calculated column count
  • Power bi calculated column distinct count
  • Power bi calculated column divide
  • Power bi calculated column divided by zero
  • Power bi calculated column contains text
  • Power bi calculated column concatenate
  • Power bi calculated column left
  • Power bi calculated column as legend
  • Power bi calculated column age
  • Power bi calculated column earlier
  • Power bi calculated column empty
  • Power bi calculated column max value
  • Power bi calculated column if an error
  • Power bi calculated column from an unrelated table
  • Power bi calculated column grand total
  • Power bi calculated column in the matrix
  • Power bi calculated column index
  • Power bi calculated column if null
  • Power bi calculated column date format
  • Power bi calculated column dateadd
  • Power bi calculated column days between two dates
  • Power bi calculated column earliest date
  • Power bi calculated column latest date
  • Power bi calculated column last year
  • Power bi calculated column last month
  • Power bi calculated column year
  • Power bi calculated column month name
  • Power bi calculated column week number
  • Power bi calculated column month and year
  • Power bi calculated column previous month
  • Power bi calculated column fiscal year
  • Power bi calculated column today’s date
  • Power bi calculated column YTD
  • Power bi calculated column multiple if statement
  • Power bi calculated column multiply
  • Power bi calculated column number format
  • Power bi calculated column nan
  • Power bi calculated column number to text
  • Power bi calculated column percentage of total
  • Power bi calculated column percentage
  • Power bi calculated column previous row
  • Power bi calculated column decimal places
  • Power bi calculated column sum by group
  • Power bi calculated column switch
  • Power bi calculated column selected value
  • Power bi calculated column sum if
  • Power bi calculated column subtraction
  • Power bi calculated column true false
  • Power bi calculated column time difference
  • Power BI calculated column use relationship
  • Power BI calculated column based on a measure
  • Power bi calculated column based on another column
  • Power bi calculated column between two tables
  • power bi calculated column count by group
  • Power bi calculated column date from DateTime
  • Power bi calculated column rankx

Table of Contents

Power bi calculated column

Calculated columns allow you to add new data to tables that already exist in your model. However, instead of querying and reading the value from the data source into the new column, create a Data Analysis Expression (DAX) expression that defines the value in the column. In Power BI Desktop, calculated columns are created using the new column feature in Report View.

The calculated column you create in Power BI appears like any other field in the Fields list, but with a special icon that indicates that the value is the result of a formula. Like any other field, you can give the column any name you like and add it to your report visualization.

Power bi calculated column vs measure

Here we will see the difference between Power BI calculated column vs the measure.

Calculated columnMeasure
The calculated column returns multiple resultsIt returns a single result
The calculation is done row by rowThe calculation is done in response to filter and field changes in the report
It needs memory to storeIt doesn’t need memory to store
The calculation is done when the report is refreshed or when it is created.Get calculated when added to the report through something like a card visual
It increases the file sizeIt doesn’t increase the file size
One can see the calculated column result in the Data viewYou can see the Measure view when you add to the visual.
It does not provide a weighted average of a columnIt provides a weighted average
Power bi calculated column vs measure

Read Power BI sync slicers

Power bi calculated column if

Here we will see an example of the Power BI calculated column if.

We will use the below table, and we will create a calculated column that will calculate if the Amount is more than 600, then return ‘Good’, else return Not Good’.

power bi calculated column if
power bi calculated column if
  • In Power BI Desktop, go to the Modelling tab -> New Column to create a calculated column.
  • Then in the Formula box write the below formula:
Column = If('Table'[Amount]>=600,"Good", "Not Good")
Microsoft power bi calculated column if
Microsoft power bi calculated column if

Now, go to the data view, you can see the calculated column is added to the table, it shows the value based on calculation.

power bi calculated column if else
power bi calculated column if else

This is an example of power bi calculated column if.

Read Line Chart in Power BI

Power bi calculated column aggregate

Here we will see an example of Power Bi calculated column aggregate.

For example, we will use the below table, and we will calculate the sum of the Amount.

Power bi calculated column aggregate
Power bi calculated column aggregate
  • In Power BI Desktop, go to the Modelling tab -> select the New column from the ribbon.
  • Then in the Formula box write the below formula:
Total Amount = SUM('Table'[Amount])
Microsoft Power bi calculated column aggregate
Microsoft Power bi-calculated column aggregate

This is an example of Power bi calculated column aggregate.

Read Power BI Group By

Power BI calculated column average

Here we will see an example of Power BI calculated column average.

For example, we will use the below table and we will create a calculated column to calculate the average of the total amount.

power bi calculated column average
power bi calculated column average
  • In Power BI, go to the Modeling tab -> select the New column from the ribbon.
  • Then write the below formula in the Formula box:
Average Amount = AVERAGE('Table'[Amount])
Microsoft power bi calculated column average
Microsoft power bi calculated column average

This is an example of a power bi calculated column average.

Power bi calculated column average by category

Here we will see an example of Power BI calculated column average by category.

For example, we will use the below table, and we will create a new column to calculate the Average Amount based on the Rate category.

power bi calculated column average by category
power bi calculated column average by category
  • In Power BI Desktop, go to the Modelling tab -> click on the New column from the ribbon.
  • Then write the below formula in the formula box:
AverageAmount based on Rate category = CALCULATE (
    Average ( 'Table'[Amount]),
    ALLEXCEPT ( 'Table','Table'[Rate] )
)
  • Now you can see the new column added to the table having the Average of Amount for Rate category.
Microsoft power bi calculated column average by category
Microsoft power bi calculated column average by category

This is an example of power bi calculated column average by category.

Read Power bi conditional column

Power bi calculated column add two columns

Here we will see an example of a Power BI calculated column adding two columns.

For example, we have the below table, and we will create a calculated column that will add the Amount and tax column.

power bi calculated column add two columns
power bi calculated column add two columns
  • In Power BI, go to the Modelling tab -> click on the New column from the ribbon.
  • Then provide the below formula in the Formula box:
Total Amount = 'Table'[Amount] +'Table'[Tax]
  • Now you can see you have added the column to the table with added result of Amount and tax.
Microsoft power bi calculated column add two columns
Microsoft power bi calculated column add two columns

This is an example of a power bi calculated column add two columns.

Power BI calculated column another table

Here we will see an example of Power BI calculated column another table.

For example, we have the below table, and we want to add a column that contains values by extracting the sales values based on id from table 2 to table 1.

Table 1

power bi calculated column across two tables
power bi calculated column across two tables

Table 2:

power bi calculated column from another table
power bi calculated column from another table
  • In Power BI Desktop, click on the Modeling tab -> then click on the New column from the ribbon.
  • Next, write the below formula in the formula box:
Sum_Tab2 = 
var t2_ID = Person[Id]

return
CALCULATE(
    SUM('Table (2)'[Sales]),
    'Table (2)'[Id] = t2_ID
)
  • Now you can see the sales value column added to the table:
power bi calculated column another table
power bi calculated column another table

This is an example of a power bi calculated column in another table.

Read Power BI date hierarchy

Power BI calculated column if and

Here we will see an example of Power BI calculated column if and.

For example, we will use the below table, and we will create a new column to check if the age is greater than equal to 30, that person is not eligible, or else it is eligible.

power bi calculated column if and
power bi calculated column if and
  • In Power BI Desktop, go to the Modelling tab -> click on the New column from the ribbon.
  • Then write the below formula in the formula box:
Criteria = IF(Person[Age]>=30, "Not Eligible","Eligible")
  • Now you can see the column gets added to the table based on the formula.
Microsft power bi calculated column if and
Microsft power bi calculated column if and

This is an example of Power BI calculated column if and.

Power bi calculated column moving average

Here we will see an example Power BI Calculated column moving average.

The moving averages approach carries the average of the time series’ most current k data values. It’s called moving because it replaces the oldest observation in the equation with the most recent value and calculates a new average every time a new observation becomes available for the time series.

As a result, the duration over which the average is calculated shifts with each passing duration.

For example, we will use the below table and we will calculate the moving average of 3 months.

power bi calculated column moving average
power bi calculated column moving average
  • In Power BI Desktop, go to the Modelling tab, and select the New column from the ribbon.
  • Then write the below formula in the formula box:
Moving_Average_3_Month = CALCULATE (
    AVERAGEX ( ALLSELECTED('MovingAverage'), 'MovingAverage'[Amount]),
    DATESINPERIOD (
        'MovingAverage'[Date],
        LASTDATE ( 'MovingAverage'[Date] ),
        -3,
        MONTH
    )
)
  • Now you can see the column get added to the table which contains the value of the Moving average of 3 month
Microsoft power bi calculated column moving average
Microsoft power bi calculated column moving average

This is an example of Power bi calculated column moving average.

Read Power BI Date Slicer

Power bi calculated column cumulative sum

Here we will see an example of Power Bi calculated column cumulative sum.

For this example, we will use the below Shift table for multiple employees, and we will calculate the cumulative sum based on the Score and half the cumulative sum for each row where the workday is False.

power bi calculated column cumilative sum
power bi calculated column cumulative sum
  • In Power BI Desktop, go to the Modelling tab -> click on the new column from the ribbon.
  • Then write the below formula in the Formula box:
Cumulative Sum = 
VAR OuterDate = Shifts[Date]
RETURN
    SUMX (
        CALCULATETABLE (
            Shifts,
            ALLEXCEPT ( Shifts, Shifts[Emp_ID] ),
            Shifts[Date] <= OuterDate
        ),
        VAR InnerDate = Shifts[Date]
        RETURN
            POWER (
                0.5,
                CALCULATE (
                    COUNTROWS ( Shifts ),
                    ALLEXCEPT ( Shifts, Shifts[Emp_ID] ),
                    Shifts[Date] <= OuterDate,
                    Shifts[Date] >= InnerDate,
                    NOT ( Shifts[Workday] )
                )
            )
                * Shifts[Score]
    )
  • Next, you can see the column added having the cumulative sum based on the formula.
Microsoft power bi calculated column cumilative sum
Microsoft power bi calculated column cumulative sum

This is an example of Power bi calculated column cumulative sum.

Read How to create a report in Power BI Desktop?

Power bi calculated column count rows

Here we will see an example of Power BI calculated column count rows.

For example, we will use the below table and we will add a column that will count the number of rows present in the table.

power bi calculated column count rows
power bi calculated column count rows
  • In Power Bi Desktop, go to the Modelling tab -> select the New column from the ribbon.
  • Then write the below formula in the Formula box:
Number of person = COUNTROWS(Person)
  • Now you can see a column get added to the table having the number of rows present in the table.
Microsoft power bi calculated column count rows
Microsoft power bi calculated column count rows

This is an example of Power BI calculated column count rows.

Power bi calculated column count if

Here we will see an example of Power bi calculated column count if.

As we know the countIf function is not available in Power BI, it is an excel function. Instead of Count If the function, we can apply the same logic using 2 functions:

  1. CountA Function: Basically it will count the number of values in the column, but it does not count the null or blank value.
  2. CountRows Function: This function counts the number of rows present in the column of the table.

For example, we will use the below table and we will count the number of people, whose age is greater than 25.

power bi calculated column count if
power bi calculated column count if
  • In Power BI Desktop, go to the Modelling tab -> select the New column from the ribbon.
  • Then write the below formula in the Formula box:
Age>25 = CALCULATE(COUNTROWS(Person), Person[Age]>=25)
  • Now you can see the column get added and have the value 1, person age> 25.
Microsoft power bi calculated column count if
Microsoft power bi calculated column count if

This is an example of Power bi-calculated column count if.

Read Power BI split column

Power bi calculated column count

Here we will see an example of the Power BI calculated column count function.

For example, we will use the below table and create a new calculated column to count the number of people.

Microsoft power bi calculated column count
Microsoft power bi calculated column count
  • In Power Bi Desktop, go to the Modelling tab -> select the New column from the ribbon.
  • Then write the below formula in the Formula box.
Number of person = COUNT(Person[Person])
  • Next, you can see the column added to the table having the number of personal values.
power bi calculated column count
power bi calculated column count

This is an example of a Power bi-calculated column count.

Power bi calculated column distinct count

Here we will see an example of Power BI calculated column distinct count.

For example, we will use the below table and count the unique person’s name present in the table.

power bi calculated column distinct count
power bi calculated column distinct count
  • In Power BI Desktop, go to the Modelling tab -> select the new column from the ribbon.
  • Then write the below formula in the Formula box:
Number of person = DistinctCOUNT(Person[Person])
  • Next, you can see the calculated column added to the table having the number of unique names.
Microsoft power bi calculated column distinct count
Microsoft power bi calculated column distinct count

This is an example of a Power BI calculated column distinct count.

Read Power Query Date

Power bi calculated column divide

Here we will see an example of Power BI calculated column divide.

For example, we will use the below table and we will calculate the profit and profit percentage.

power bi calculated column divide
power bi calculated column divide
  • In Power BI Desktop, go to the Modelling tab -> click on the New column from the ribbon.
  • Then write the below formula to get the Profit.
Profit = 'Product'[SP]-'Product'[CP]
  • Next, create another new calculated column to calculate the profit percentage using the below formula:
Profit percentage = DIVIDE('Product'[Profit]*100,'Product'[CP],0)

Now you can see the Profit column and Profit Percentage column are added to the table.

Microsoft power bi calculated column divide
Microsoft power bi calculated column divide

This is an example of a power bi calculated column divide.

Read Power Query Examples

Power bi calculated column divided by zero

Here we will see an example of a Power BI calculated column divided by zero.

For example, we will use the below table, and we will use column 1 as the numerator and column 2 as the denominator, which contains a 0 value.

Power bi calculated column divided by zero
Power bi calculated column divided by zero
  • In Power BI Desktop, go to the Modelling tab -> select the New column from the ribbon.
  • Then write the below formula in the formula box:
Result= DIVIDE('Table (3)'[Column1],'Table (3)'[Column2],0)
  • Now, you can see the column added to the table based on the above formula.
Microsoft Power bi calculated column divided by zero
Microsoft Power bi-calculated column divided by zero

This is an example of a Power BI calculated column divided by zero.

Power bi calculated column contains text

Here we will see an example of a Power BI calculated column containing the text.

For example, we will use the table below and create a new column that will check whether the Person column contains the ‘Person 1’ value and return ” contains the text”, or else “does not contain the text”.

power bi calculated column contains text
power bi calculated column contains text
  • In Power BI Desktop, go to the Modeling tab -> select the New column from the ribbon.
  • Then write the below formula in the formula box.
Text or not = if (Person[Person]= "Person 1", "Contains text", " does not contain text")
Microsoft power bi calculated column contains text
Microsoft power bi calculated column contains text

This is an example of a Power bi-calculated column containing text.

Read Create table using Power Query in Power BI

Power bi calculated column concatenate

Here we will see an example of Power BI calculated column concatenate.

For example, we will use the below table and we will concatenate the Person column and the id column.

power bi calculated column concatenate
power bi calculated column concatenate
  • In Power BI Desktop, go to the Modelling tab -> then click on the New column from the ribbon.
  • Then write the formula in the Formula box:
id and person = Person[Id] & "-" & Person[Person]
  • Now you can see the new column added to the table having the concatenated value.
Microsoft power bi calculated column concatenate
Microsoft power bi calculated column concatenate

This is an example of a Power bi-calculated column concatenate.

Power BI calculated column left

Here we will see an example of the Power BI calculated column left.

For example, we will use the below table and we will get the left 3 letters from the Day name column. Let’s say the Day name is Monday, so the first 3 letters will be ‘Mon’.

Power BI calculated column left
Power BI calculated column left
  • In Power BI Desktop, go to the Modeling tab, then click on the New column from the ribbon.
  • Then write the below formula in the formula box:
Column = LEFT('Table 6'[Days name],3)
Microsoft Power BI calculated column left
Microsoft Power BI calculated column left

This is an example of the Power BI calculated column left.

Read Power BI integration with PowerApps Portals

power bi calculated column as legend

Here we will see an example of a Power BI calculated column as a legend.

For example, we will use the below table and we will use the Criteria calculated column as a legend in the Stacked column chart.

Microsoft power bi calculated column as legend
Microsoft power bi calculated column as legend
  • In Power BI Desktop, select the stacked bar chart in the visualization pane.
  • Then provide the Age field as Axis, Criteria (calculated column) as Legend, and Sales field as value.
power bi calculated column as legend
power bi calculated column as legend

This is an example of a Power BI calculated column as a legend.

Power bi calculated column age

Here we will see an example of Power BI calculated column age.

For example, we will use the below table and we will calculate the Age of each person. Here, I have DOB column that’s contains the date of birth and we will calculate the age from the DOB column.

Power bi calculated column age
Power bi calculated column age
  • In Power BI Desktop, go to the Modelling tab -> select the New column from the ribbon.
  • Then write the below formula in the formula box:
Age = DATEDIFF(Person[DOB],TODAY(),YEAR)
Microsoft Power bi calculated column age
Microsoft Power bi-calculated column age

This is an example of Power bi calculated column age.

Read Power BI if date + 27 Examples

Power bi calculated column earlier

Here we will see an example of the Power BI calculated column earlier.

For example, we will use the below table, and we will sum the amount for each product.

Power bi calculated column earlier
Power bi calculated column earlier
  • In Power BI Desktop, go to the Modeling tab -> click on the New column from the ribbon.
  • Then write the below formula in the formula box:
Total for product = CALCULATE(SUM('Table 5'[Amount]),
           FILTER(ALL('Table 5'),
            'Table 5'[Product]=EARLIER('Table 5'[Product])
                 )
          )
  • Now you can see the table added to the table having the total amount for each product.
Microsoft Power bi calculated column earlier
Microsoft Power bi-calculated column earlier

This is an example of a Power bi-calculated column earlier.

Power bi calculated column empty

Here we will see an example of a Power BI calculated column empty.

For example, we will use the below table and we will check any empty or blank values in the Sales column. if there are blank or empty values it returns true or else false.

power bi calculated column empty
power bi calculated column empty
  • In Power BI Desktop, go to the Modelling tab -> select the New column from the ribbon.
  • Then write the below formula in the Formula box:
Column = ISBLANK('Person'[Sales])

Now you can see the column added to the table has true and false values based on whether the sales column is empty or not.

Microsoft power bi calculated column empty
Microsoft power bi calculated column empty

This is an example of a Power BI calculated column empty.

Read Power BI IF + 31 Examples

Power BI calculated column max value

Here we will see an example of Power BI calculated column max value.

For example, we will use the below table and we will get the max amount value using Dax in a calculated column.

power bi calculated column max value
power bi calculated column max value
  • In the Power BI desktop, go to the Modelling tab -> click on the New column from the ribbon.
  • Then write the below formula in the formula box:
Max Amount = MAX('Table 5'[Amount])
  • Now you can see the max value in the newly added column to the table in Power BI.
Microsoft power bi calculated column max value
Microsoft power bi calculated column max value

This is an example of a Power Bi calculated column max value.

Power bi calculated column if an error

Here we will see an example of Power BI calculated column if an error.

For example, we will use the below table, and we will divide column 1 with column 2. If we get an error in the output, then return 0, else return the output.

Power bi calculated column if an error
Power bi calculated column if an error
  • In Power BI Desktop, go to the Modeling tab -> then click on the New column from the ribbon
  • Then write the below formula in the formula box:
If error eq 0 = IFERROR('Table (3)'[Column1]/'Table (3)'[Column2],0)

Now you can see the column added to the table having 0 value if there is an error.

Microsoft Power bi calculated column if an error
Microsoft Power bi calculated column if an error

This is an example of Power Bi calculated column if an error.

Read Power BI Change Data Type of a Column

Power bi calculated column from an unrelated table

Here we will see an example of the Power BI Calculated column from another table.

For example, we will use two unrelated tables, and we will create a sales column in Table 1 by using LookupValue() in a calculated column.

Table 1:

power bi calculated column from unrelated table
power bi calculated column from an unrelated table

Table 2:

 Microsoft power bi calculated column from unrelated table
Microsoft power bi calculated column from an unrelated table
  • In the Power Bi desktop, go to the Modelling tab -> click on the New column from the ribbon.
  • Then in the Formula box write the below formula:
Sales = 
LOOKUPVALUE (
    'Table (2)'[Sales],
   'Table (2)'[Id] , Persons[Id]
)
  • Now you can see a new column added to the table having sales value.
power bi calculated column lookupvalue
power bi calculated column lookup value

This is an example of Power bi calculated column lookup value

Power bi calculated column grand total

Here we will see an example of Power BI calculated column grand total.

For example, we will use the below table and we will calculate the grand total of sales and grand total percentage using the calculated column in Power BI.

power bi calculated column grand total
power bi calculated column grand total
  • In Power BI Desktop, go to the Modelling tab -> select the New column from the ribbon.
  • Then write the below formula in the formula box to calculate the grand total sales:
Grand total sales = SUM('Person'[Sales])
  • Now, to calculate the grand total percentage, create another New column.
  • Then write the below formula in the Formula box:
% of Grand Total = 
DIVIDE (
    SUM ( Persons[Sales]),
    CALCULATE ( SUM ( Persons[Sales] ), ALL ( Persons[Person]) )
)
  • Now you can see two columns get added to the table, one for grand total and another for grand total percentage.
Microsoft power bi calculated column grand total
Microsoft power bi calculated column grand total

This is an example of Power Bi calculated column grand total.

Read Access to the resource is forbidden error in Power BI

Power bi calculated column in the matrix

Here we will see an example of the Power BI calculated column in the Matrix

For example, we will use the below table, so in this table, we have two calculated columns ( Profit and Profit percentage) and we will add these two calculated columns to Matrix visual.

Profit = 'Product'[SP]-'Product'[CP]
Profit percentage = DIVIDE('Product'[Profit]*100,'Product'[CP],0)
power bi calculated column in matrix
power bi calculated column in the matrix
  • In the Power BI Desktop report view, select the Matrix visual from the visualization pane.
  • Then in the Row field, -> drag the Product, SP, CP and in value drag the Profit and Profit percentage column.
  • Then in the Format pane, under ‘Row header’, turn off the Stepped layout.
Microsoft power bi calculated column in matrix
Microsoft power bi calculated column in the matrix

This is an example of Power BI calculated column in the matrix.

Power bi calculated column index

Here we will see an example of a Power BI calculated column index.

For example, we will use the below table and we will create a calculated column to provide the index based on the total value and customer. We will set the highest value as index 1 and so on.

Power bi calculated column index
Power bi calculated column index
  • In Power BI Desktop, go to the Modelling tab -> select the New column from the ribbon.
  • Then write the DAX formula in the formula box:
SuperRank = 
    VAR __table = FILTER(ALL('Table6'),[Total] > EARLIER('Table6'[Total]))
    VAR __tableSame = FILTER(ALL('Table6'),[Total] = EARLIER('Table6'[Total]) && [Customer] < EARLIER(Table6[Customer]))
RETURN
    COUNTROWS(__table) + 1 + COUNTROWS(__tableSame)
  • Now, you can see a new column added to the table, which provides the index value based on the total value and customer.
Microsoft Power bi calculated column index
Microsoft Power BI calculated column index

This is an example of Power BI calculated column index.

Read Power BI convert hours to minutes

Power BI calculated column if null

Here we will see an example of a Power BI calculated column if null.

For example, we will use the below table, and we will check if the value of the CP column is null or blank then it returns zero.

power bi calculated column if null
power bi calculated column if null
  • In Power BI Desktop, go to the Modeling tab -> click on the New column from the ribbon.
  • Then write the below formula in the formula box.
Column = if('Product (2)'[CP] = 0 || BLANK(), 0, 'Product (2)'[CP])
  • Now you can see a column added to the table having 0 value instead of null values.
Microsoft power bi calculated column if null
Microsoft power bi calculated column if null

This is an example of power bi calculated column if null.

Power bi calculated column date format

Here we will see an example of Power BI calculated column date format.

For example, we will use the below table and we will format the below date column as MM-dd-yyyy.

power bi calculated column date format
power bi calculated column date format
  • In Power BI Desktop, go to the Modeling tab -> click on the New column from the ribbon.
  • Then write the below formula in the Formula box.
Formated date = FORMAT(MovingAverage[Date], "MM-dd-yyyy")
  • Now you can see the new column added to the table having the formatted date.
Microsoft power bi calculated column date format
Microsoft power bi calculated column date format

This is an example of Power BI calculated column date format.

Read Power BI convert yyyymmdd to date

Power BI calculated column dateadd

Here we will see an example of Power BI calculated column date add.

For example, we will use the below table and we will add two days to the date column.

Power BI calculated column dateadd
Power BI calculated column dateadd
  • In Power Bi Desktop, go to the Modelling tab -> click on the New column from the ribbon.
  • Then write the below formula in the formula box:
Dateadd 2 days = Format (MovingAverage[Date] +2, "dddd")
  • Now you can see a new column added to the table which contains days’ names having 2 days ahead of the date column.
Microsoft Power BI calculated column dateadd
Microsoft Power BI calculated column dateadd

This is an example of Microsoft Power BI calculated column dateadd.

Power bi calculated column days between two dates

Here we will see an example of Power BI Calculated column days between two dates.

For example, we will use the below table, and we will calculate the number between ship date and order date.

power bi calculated column days between two dates
power bi calculated column days between two dates
  • In Power BI Desktop, go to the Modelling tab -> select the New column from the ribbon.
  • Then write the below formula in the formula box:
Day = DATEDIFF('Product'[Order date],'Product'[Ship date],DAY)
  • Now you can see the new column added to the table having the number of days.
Microsoft power bi calculated column days between two dates
Microsoft power bi calculated column days between two dates

This is an example of Power Bi calculated column days between two dates.

Read Get Month Name from Month Number in Power BI

Power bi calculated column earliest date

Here we will see an example of Power BI calculated column earliest date.

For example, we will use the below table and we will get the earliest date based on Email by and Email subject.

power bi calculated column earliest date
power bi calculated column earliest date
  • In Power BI Desktop, go to the Modelling tab -> click on the New column from the ribbon.
  • Then write the below formula in the formula box:
Earliest Date = var Emailby ='Table 4'[Email by]
var EmailSubject = 'Table 4'[Email Subject]
Return
MINX( Filter(ALL('Table 4'),'Table 4'[Email by]=Emailby && 'Table 4'[Email Subject]=EmailSubject), 'Table 4'[Date])
  • Now you can see the calculated column added to the table, having the earliest date for Email by and Email subject.
Microsoft power bi calculated column earliest date
Microsoft power bi calculated column earliest date

This is an example of a Power bi-calculated column earliest date.

Power bi calculated column latest date

Here we will see an example of Power BI calculated column latest date.

For example, we will use the table below and create a calculated column, in which we will use the Max () to get the latest date from the date column.

power bi calculated column latest date
power bi calculated column latest date
  • In Power BI Desktop, go to the Modelling tab -> click on the New column from the ribbon.
  • Then write the below formula in the formula box:
Latest order date = MAX('Product'[Order date])
  • Now, you can see the column added to the table having the latest date in Power BI.
Microsoft power bi calculated column latest date
Microsoft power bi calculated column latest date

This is an example of power bi calculated column latest date.

Read Power bi shared dataset permissions management

Power bi calculated column last year

Here we will see an example of the Power BI calculated column last year.

For example, we will use the below table, and we will create a calculated column that will calculate the total amount of the previous year based on product and customer.

Power bi calculated column last year
Power bi calculated column last year
  • In Power BI Desktop, go to the Modelling tab, and select the New column from the ribbon.
  • Then write the below formula in the formula box.
PY_Price = 
CALCULATE (
    SUM ( 'Table 6'[Amount] ),
    ALLEXCEPT ('Table 6','Table 6'[Customer], 'Table 6'[Product] ),
    PREVIOUSYEAR( ('Table 6'[Date] )
))
  • Now you can see the column added to the table, having the previous year’s total amount based on Customer and product.
Microsoft Power bi calculated column last year
Microsoft Power bi calculated column last year

This is an example of Power bi calculated column last year.

Read Power BI nan error (Not a number) while dividing by Zero

Power bi calculated column last month

Here we will see an example of the Power BI calculated column last month.

For example, we will use the below table and we will calculate the total sales for the previous month based on product and customer.

Power bi calculated column last month
Power bi calculated column last month
  • In Power BI Desktop go to the Modeling tab -> select the New column from the ribbon.
  • Then write the below formula in the formula box:
PM_Price = 
CALCULATE (
    SUM ( 'Table 6'[Amount] ),
    ALLEXCEPT ('Table 6','Table 6'[Customer], 'Table 6'[Product] ),
    PREVIOUSMONTH( ('Table 6'[Date] )
))
  • Now you can see the column added to display the total amount for the previous month.
power bi calculated column previous month
power bi calculated column previous month

This is an example of Power bi calculated column previous month.

Power bi calculated column year

Here we will see an example of Power BI calculated column year.

For example, we will use the below table, to get the year from the date column using DAX.

Power bi calculated column year
Power bi calculated column year
  • In Power BI Desktop, go to the Modeling tab, then select the New column from the ribbon.
  • Then write the below formula in the formula box.
Year = YEAR(MovingAverage[Date])
  • Now you can see the column get added to the table having year value.
Microsoft Power bi calculated column year
Microsoft Power bi calculated column year

This is an example of Power bi calculated column year.

Read Power BI report using People Picker Field

Power bi calculated the column month name

Here we will see an example of Power BI calculated column month name.

For example, we will use the below table and we will create a calculated column to get the Month name using DAX.

power bi calculated column month name
power bi calculated column month name
  • In Power BI Desktop, go to the Modelling tab -> click on the New column from the ribbon.
  • Then write the below formula in the formula box:
Month name = FORMAT('Table 4'[Date],"MMMM")
  • Now you can see the calculated column get created having the Month name.
Microsoft power bi calculated column month name
Microsoft power bi calculated column month name

This is an example of Microsoft power bi calculated column month name.

Power bi calculated column week number

Here we will see an example of Power BI calculated column week number

For example, we will use the below table and get the week number based on the date column.

Microsoft Power bi calculated column Weeknum
Microsoft Power calculated column Weeknum
  • In Power BI, go to the modeling tab, then click on the New column from the ribbon.
  • Then write the below DAX formula in the formula box:
Week num = WEEKNUM(MovingAverage[Date])
  • Now you can see the calculated column having the week number.
Power bi calculated column Weeknum
Power bi calculated column Weeknum

This is an example of Power bi calculated column Weeknum.

Read Data Labels in Power BI

Power bi calculated column month and year

Here we will see an example of Power BI calculated column month and year.

For example, we will use the below table to get the month and year using Dax in a calculated column.

power bi calculated column month and year
power bi calculated column month and year
  • In Power BI Desktop, go to the Modelling tab -> click on the New column from the ribbon.
  • Then write the below formula in the Formula box:
Month and year = Format('Table 4'[Date], "MM-yyyy")
  • Now, you can see the column added to the table contains the Month and Year from the date column in Power BI.
Microsoft power bi calculated column month and year
Microsoft power bi calculated column month and year

This is an example of power bi calculated column month and year.

Power bi calculated column fiscal year

Here we will see an example of Power BI calculated column fiscal year or Financial year.

For this example, we will use the sample Financial table, and we will calculate the financial year from the date column. In the date column the date from April 2021 to April 2022 then the output should be FY21/22.

  • In Power BI Desktop, go to the Modelling tab -> click on the New column from the ribbon.
  • Then write the below formula in the formula box:
Financial Year = 
VAR fy =
    IF (
        MONTH ( Table1[Date]) <= 3,
        VALUE ( FORMAT ( Table1[Date], "YY" ) ) - 1,
        VALUE ( FORMAT ( Table1[Date], "YY" ) )
    )
RETURN
    CONCATENATE ( "FY", CONCATENATE ( fy, CONCATENATE ( "/", fy + 1 ) ) )
  • Now you can see the Financial year column added to the table in Power BI.
power bi calculated column fiscal year
power bi calculated column fiscal year

This is an example of power bi calculated column fiscal year.

Power bi calculated column today’s date

Here we will see an example of the Power BI calculated column on today’s date.

  • In the Power BI desktop, to get today’s date in the calculated column, go to the Modelling table -> click on the New column from the ribbon.
  • Then write the below formula in the formula box:
Today's date = TODAY()
Power bi calculated column today's date
Power bi calculated column today’s date

This is an example of Power bi calculated column today’s date.

Read Power bi table visualization

Power bi calculated column YTD

Here we will see an example of Power BI calculated column YTD.

For example, we will use the sample financial table and we will calculate year-running total sales.

  • In Power BI Desktop, go to the modeling tab -> click on the New column from the ribbon.
  • Then write the below formula in the formula box:
Calculated Column = TOTALYTD ( SUM ( Table1[ Sales] ) , Table1[Date] )
Power bi calculated column YTD
Power bi calculated column YTD

This is an example of Power bi calculated column YTD.

Power BI calculated column multiple if statement

Here we will see an example of a Power BI calculated column multiple if statement.

For example, we will use the below table and we will create a range calculated column based on the sales range.

Power BI calculated column multiple if statement
Power BI calculated column multiple if statement
  • In Power BI Desktop, go to the Modelling tab-> click on the New column from the ribbon.
  • Then write the below formula in the Formula box:
Range = IF('Table 9'[Sales]>=10 && 'Table 9'[Sales] <= 30, "10-30", IF('Table 9'[Sales] >= 30 && 'Table 9'[Sales] <= 40, "30-40", IF('Table 9'[Sales] >= 40, "greater than 40",BLANK())))
  • Now you can see the new column added to the table having range based on sales.
Microsoft Power BI calculated column multiple if statement
Microsoft Power BI calculated column multiple if statement

This is an example of Power BI calculated column multiple if statement.

Power bi calculated column multiply

Here we will see an example of Power BI calculated column multiply.

For example, we will use the below table and we will multiply the SP and quantity.

Power bi calculated column multiply
Power bi calculated column multiply
  • In Power BI Desktop, go to the Modeling tab -> then select the New column from the ribbon.
  • Then write the below formula in the formula box:
Result = 'Product (2)'[SP]*'Product (2)'[Quantity]
Microsoft Power bi calculated column multiply
Microsoft Power BI calculated column multiply

This is an example of Power BI calculated column multiply.

Power bi calculated column number format

Here we will see an example of Power BI calculated column number format.

For example, we will use the below table, the sales column is the calculated column, and we will format the sales column as a number.

Microsoft Power bi calculated column number format
Microsoft Power BI calculated column number format
  • To format the sales calculated column as a number in Power BI, select the column -> to go to the Column tools tab -> then change the format General to the Whole number.
Power bi calculated column number format
Power bi calculated column number format

This is an example of the Power Bi calculated column number format.

Power bi calculated column nan

Here we will see an example of Power BI calculated column nan.

For example, we will use the below table, here we will replace Nan with 0. Basically in visual, we will get Nan, when we will divide 0 by 0.

Power bi calculated column nan
Power bi calculated column nan
  • In Power BI Desktop, go to the Modelling tab -> then click on the new column from the ribbon.
  • Then write the below formula in the formula box:
Result = if('Order'[Amount 2]=0,0,'Order'[Amount 1]/'Order'[Amount 2])
  • Now I added the calculated column to the Card visual, it is showing a 0 value.
Microsoft Power bi calculated column nan
Microsoft Power bi calculated column nan

This is an example of Power BI calculated column nan.

Power BI calculated column number to text

Here we will see an example of Power BI calculated column number to text.

For example, we will use the below table and we will convert the result column from a decimal number to text.

Power bi calculated column number to text
Power bi calculated column number to text
  • In Power BI Desktop, go to the Modelling tab -> click on the New column from the ribbon.
  • Then write the below formula in the formula box:
Column = CONVERT('Order'[Result],STRING)
  • Now you can see the data type of the newly calculated column changed to text.
Microsoft Power bi calculated column number to text
Microsoft Power BI calculated column numbers to text

This is an example of Power BI calculated column numbers to text.

Power bi calculated column percentage

Here we will see an example of Power BI calculated column percentage.

For example, we will use the below table and we will calculate the profit percentage.

Power bi calculated column percentage
Power bi calculated column percentage
  • In Power BI Desktop, go to the Modelling tab -> click on the New column from the ribbon.
  • Then write the below formula in the formula box:
Profit percentage = DIVIDE('Product'[Profit],'Product'[CP],0)
  • Now click on the calculated column and change the format to a percentage.
Microsoft Power bi calculated column percentage
Microsoft Power Bi calculated column percentage

This is an example of Power Bi calculated column percentage.

Power bi calculated column percentage of total

Here we will see an example of Power BI calculated column percentage of the total.

For example, we will use the below table and we will calculate the total of Amount 1 based on the product, then we will calculate the percentage of grand total based on the product.

Power bi calculated column percentage of total
Power bi calculated column percentage of total
  • In Power BI Desktop, go to the Modelling tab -> select the New column from the ribbon.
  • Then write the below formula in the formula box to calculate the Total Amount based on the product:
Total Amount 1 = CALCULATE(SUM('Order'[Amount 1]), ALLEXCEPT('Order','Order'[Product]))
Microsoft Power bi calculated column percentage of total
Microsoft Power bi calculated column percentage of total
  • Now to calculate the grand total percentage, create a new column, then write the below formula in the formula box:
grand total percentage = DIVIDE('Order'[Amount 1],'Order'[Total Amount 1],0)
  • Then change the format to the percentage of the calculated column in Power BI Desktop.
calculated column percentage of total in Microsoft Power bi
calculated column percentage of total in Microsoft Power bi

This is an example of Power bi calculated column percentage of total

Power bi calculated column previous row

Here we will see an example of Power BI calculated column previous row.

For example, we will use the below table and we will get the sales value from the previous row.

Power bi calculated column previous row
Power bi calculated column previous row
  • In Power BI Desktop, go to the Modelling tab -> click on the New column from the ribbon.
  • Then write the below formula in the formula box:
Calculated Column = 
VAR PreviousRow =
    TOPN (
        1,
        FILTER (
            'Table 9',
            'Table 9'[Code]< EARLIER ( 'Table 9'[Code] )
        ),
        [Code], DESC
    )
VAR PreviousValue =
    MINX ( PreviousRow, [Sales] )
RETURN
    'Table 9'[Sales] + PreviousValue
  • Now you can see the column added to the table, having the previous row value.
Microsoft Power bi calculated column previous row
Microsoft Power bi calculated column previous row


Power bi calculated column decimal places

Here we will see an example of Power BI calculated column decimal places.

For example, we will use the below table, and we will use the Profit percentage(calculated column). This column contains the decimal numbers, we will change them into 2 decimal places from 4 decimal places.

power bi calculated column decimal places
power bi calculated column decimal places

In Power BI Desktop select the Column -> in the Column tools under Format box, change the number of decimal placed 4 -> 2.

Microsoft power bi calculated column decimal places
Microsoft power bi calculated column decimal places

This is an example of Power BI calculated column decimal places.

Power bi calculated column sum

Here we will see an example of Power Bi calculated column sum.

For example, we will use the below table and we will calculate the total sales.

Power bi calculated column sum
Power bi calculated column sum
  • In Power BI Desktop, go to the Modelling tab -> select the new column from the ribbon.
  • Then write the below formula in the formula box:
Total sales = Sum('Table 9'[Sales])
  • Now you can see the column added to the table having total sales.
Microsoft Power bi calculated column sum
Microsoft Power bi calculated column sum

This is an example of Power BI calculated column Sum.

Power bi calculated column sum by group

Here we will see an example of Power BI calculated column sum by the group.

For example, we will use the below table and we will calculate the total sum by the group.

power bi calculated column sum by group
power bi calculated column sum by group
  • In Power BI Desktop, go to the Modelling tab -> select the New column from the ribbon.
  • Then write the below formula in the formula box:
Sum of Quantity = CALCULATE(Sum('Table 7'[Quantity]), ALLEXCEPT('Table 7','Table 7'[Category]))
  • Now you can see the column having the total sum of quantity based on category.
Microsoft power bi calculated column sum by category
Microsoft power bi calculated column sum by category

This is an example of Power BI calculated column sum by category.

Power bi calculated column switch

Here we will see an example of a Power BI calculated column switch.

For example, we will use the below table and if the number of days <= 5 and action <=2 then Green, else if the number of days> 5 and Number of Days <8 and Action<=2 then Yellow, else if the number of Days >=8 and action<=2 then Red, else if the number of Days<=15 and Action<=3 then Blue, else if Number of Days >15 and Action>3 THEN Orange, else Violet.

power bi calculated column switch
power bi calculated column switch
  • In Power BI Desktop, go to the Modelling table and click on the new column from the ribbon.
  • Then write the below formula in the formula box:
Column = 
SWITCH (
    TRUE (),
    'Table 8'[No.of Days] < 5
        && 'Table 8'[Action] <= 2, "Green",
    'Table 8'[No.of Days] < 8
        && 'Table 8'[Action] <= 2, "Yellow",
   'Table 8'[No.of Days] >= 8
        && 'Table 8'[Action] <= 2, "Red",
    'Table 8'[No.of Days] <= 15
        && 'Table 8'[Action] <= 3, "blue",
   'Table 8'[No.of Days] >= 15
        && 'Table 8'[Action] > 3, "Orange",
    "Violet"
)
  • Now you can see the column added to the table based on the DAX formula.
power bi calculated column switch statement
power bi calculated column switch statement

This is an example of a Power BI calculated column switch statement.

Power bi calculated column selected value

Here we will see an example of Power BI calculated column selected values.

For example, we will use the table below and create 2 categories, Weekend (Sunday and Saturdays) and WeekDays based on days. We will add the column to the slicer, based on the Weekdays and Weekends it will show the Total Amount.

Power bi calculated column selected value
Power bi calculated column selected value
  • In Power BI Desktop, go to the Modelling tab -> then click on the New column from the ribbon.
  • Then write the below formula in the Formula box:
Selectedvalue = if(MovingAverage[Dateadd 2 days] = "Saturday" || MovingAverage[Dateadd 2 days] = "Sunday", "Weekend", "Weekdays")
  • Now you can see the column added to the table has values based on the formula.
Microsoft Power bi calculated column selected value
Microsoft Power bi-calculated column selected value
  • Next in the Power Bi Desktop Report view, select the slicer and add the Selected value column.
  • Then add the Card visual and add the Amount column to the visual. By selecting the values in Slicer, you can see the Total Amount in Card visual.
calculated column selected value in Microsoft Power bi
calculated column selected value in Microsoft Power bi

This is an example of a Power BI calculated column selected value.

Power bi calculated column sum if

Here we will see an example of Power BI calculated column sum if.

For example, we will use the below table, and we will sum the amount, if the Value in the SelectedValue column is a Weekend.

power bi calculated column sum if
power bi calculated column sum if
  • In Power BI Desktop, go to the Modelling tab -> then select the New column from the ribbon.
  • Then write the below formula in the formula box:
Weekend Total Amount = SUMX(FILTER(MovingAverage, MovingAverage[Selectedvalue]="Weekend"),MovingAverage[Amount])
  • Now you can see the column added to the table having the total Amount based on Weekend value.
Microsoft power bi calculated column sum if
Microsoft power bi calculated column sum if

This is an example of Power BI calculated column Sum if

Power bi calculated column subtraction

Here we will see Power BI calculated column subtraction.

For example, we will use the below table to calculate the profit by subtracting the sp value from the CP value.

Power bi calculated column subtraction
Power bi calculated column subtraction
  • In Power BI Desktop, go to the Modelling tab -> select the New column icon from the ribbon.
  • Then write the below formula in the formula box:
Profit = 'Product'[SP]-'Product'[CP]
  • Now you can see the new column added to the table having the Profit value.
Microsoft Power bi calculated column subtraction
Microsoft Power Bi calculated column subtraction

This is an example of Power Bi calculated column subtraction.

Power bi calculated column true false

Here we will see an example of Power BI calculated column true false.

For example, we will use the below table, and if the ‘Status on Date’ column contains a ‘Closed’ value, then it returns true or else false.

Power bi calculated column true false
Power bi calculated column true false
  • In Power BI Desktop, go to the Modelling tab -> click on the New column from the ribbon.
  • Then write the below formula in the formula box:
True/Falsebased on Status = if(Project[Status on Date]="Closed", TRUE(),FALSE())
  • Now, you can see a new column added to the table, having a true/false value based on the formula.
Microsoft Power bi calculated column true false
Microsoft Power bi calculated column true false

This is an example of Power bi calculated column true false.

Power bi calculated column time difference

Here we will see an example of Power BI calculated column time difference.

For example, we will use the below table and we will add a calculated column to get the hour difference between OrderDateTime and ShipDateTime.

Power bi calculated column time difference
Power bi calculated column time difference
  • In Power BI Desktop, go to the Modelling tab -> click on the New column from the ribbon.
  • Then write the below formula in the formula box:
Time diff = DATEDIFF('Table B'[Order DateTime],'Table B'[Ship DateTime],HOUR)
  • Now a new column is added to the table having hours value in Power BI
Microsoft Power bi calculated column time difference
Microsoft Power bi calculated column time difference

This is an example of Power bi calculated column time difference.

Power BI calculated column use relationship

Here we will see an example of the Power bi calculated column UseRelationship().

For example, we will use the below table Sales and Date table (calculated table), to get the Year value (from the Date table) based on the Order Date and Delivery date.

Sales table

power bi calculated column use relationship
power bi calculated column use relationship

The date table is a calculated table, with the below formula I have created a Calculated Date table in Power BI.

Date = 
ADDCOLUMNS(
    CALENDARAUTO (),
    "Month", FORMAT ( [Date], "mmmm" ),
    "Month Number", MONTH ( [Date] ),
    "Year", "CY " & YEAR ( [Date] )
)

So both the table have 2 relationships, i.e.:

  • Date column ( Date table) -> Order Date (Sales table) (Active relationship)
  • Date column (Date table) -> Delivery Date (Sales table) (InActive relationship)

To get the Year from the Date table for the Order date, it is easy to get in the calculated column as well as in measure because the relationship is active so we can use Related () or we can use UseRelationship inside the Calculate() as filter context. The UseRelationsip () lets you awhile change the active and inactive relationship.

OrderYear = RELATED ( 'Date'[Year] )
Order Year = CALCULATE (VALUES ( 'Date'[Year] ),USERELATIONSHIP ( 'Date'[Date], Sales[OrderDate]))
Microsoft power bi calculated column use relationship
Microsoft power bi calculated column use relationship

But if you want to get the Delivery year column from the Date table, in the measure we can easily achieve the Delivery year, by using UseRelationship, even if the relationship is inactive, we cannot get the same result in the calculated column because Calculate() performs context transition before UseRelationship () taking into an account. So after the context transition happened, later UseRelationship changed into an active relationship, as a consequence the UseRelationship() is ineffective.

DeliveryYear = CALCULATE (VALUES ( 'Date'[Year] ),USERELATIONSHIP ( Sales[DeliveryDate],'Date'[Date]))
calculated column use relationship in Microsoft power bi
calculated column use relationship in Microsoft power bi

The solution is by removing the Filter on the Date column data or we can use LOOKUPVALUE(), without relying on the relationship.

DeliveryYear = CALCULATE (VALUES ( 'Date'[Year] ),CALCULATETABLE( Sales,
USERELATIONSHIP ( Sales[DeliveryDate],'Date'[Date]), REMOVEFILTERS('Date')
))
DeliveryYear = CALCULATE (VALUES ( 'Date'[Year] ),CALCULATETABLE( Sales,
USERELATIONSHIP ( Sales[DeliveryDate],'Date'[Date]), REMOVEFILTERS('Date')
))
calculated column use relationship in power bi
calculated column use relationship in power bi

This is an example of a Power BI calculated column use relationship.

Power BI calculated column based on a measure

Here we will see an example of a Power Bi calculated column based on a measure.

For example, we will use the below table and we will create a measure to calculate the Total revenue. Then we will create a calculated column to get the total revenue based on customers.

Power BI calculated column based on a measure
Power BI calculated column based on a measure
  • In Power BI Desktop, go to the Modelling tab -> click on the New Measure from the ribbon.
  • Then write the below formula in the formula box:
Total Revenue = SUM('Table B'[Revenue])
  • Next, from the Modeling tab -> click on the New column from the ribbon.
  • Then write the below formula in the formula box:
TotalRevenue for Customer = CALCULATE([Total Revenue],
           FILTER(ALL('Table B'),
            'Table B'[Customer No.]=EARLIER('Table B'[Customer No.])
                 )
          )
  • Now you can see a new column added to the table with measures in Power BI.
Microsoft Power BI calculated column based on a measure
Microsoft Power BI calculated column based on a measure

This is an example of a Power BI calculated column based on a measure.

Power bi calculated column based on another column

Here we will see an example of a Power BI calculated column based on another column.

For example, we have two sample tables that are not in a relationship i.e. Table B ( Customer Number and revenue) and Table c (distinct customer number and Customer name). So we will create a calculated column in Table c that will calculate the sum of revenue for the customers.

Table B

power bi calculated column based on another column
power bi calculated column based on another column

Table c

Microsoft power bi calculated column based on another column
Microsoft power bi calculated column based on another column
  • In Power BI Desktop, go to the Modelling tab -> click on the new column from the ribbon.
  • Then write the below formula in the formula box:
Column = 
CALCULATE (
    SUM ( 'Table B'[Revenue] ),
    FILTER ( 'Table B', 'Table B'[Customer No.] = 'Table c'[Customer No.])
)
  • Now you can see the calculated column added to the table based on another column.
calculated column based on another column in Microsoft power bi
calculated column based on another column in Microsoft power bi

This is an example of a Power Bi calculated column based on another column

Power bi calculated column between two tables

Here we will see an example of a Power BI calculated column between two tables.

For example, we have below two tables (are in Many to one relationship) i.e. Table B and Table c. We will calculate the Total revenue when both the table are relatable.

Table B

Power bi calculated column between two tables
Power bi calculated column between two tables

Table C

Microsoft Power bi calculated column between two tables
Microsoft Power bi calculated column between two tables
  • In Power BI Desktop, go to the Modelling tab, then click on the New column from the ribbon.
  • Then write the below formula in the formula box:
Total revenue = 
CALCULATE ( SUM ( 'Table B'[Revenue] ) )
  • Now you can see a new column added to the table having total revenue.
calculated column between two tables in Microsoft Power bi
calculated column between two tables in Microsoft Power bi

This is an example of a Power BI calculated column between two tables.

power bi calculated column count by group

Here we will see an example of Power BI calculated column count by the group.

For example, we will use the below table, and we will count the number of products in each group.

power bi calculated column count by group
power bi calculated column count by group
  • In Power BI Desktop, go to the Modelling tab -> click on the New column from the ribbon.
  • Then write the below formula in the formula box:
Count the product each category = COUNTROWS(FILTER('Table 5', 'Table 5'[Product] = EARLIER('Table 5'[Product])))
  • Now you can see the column added to the table, having counts value for the product group.
Microsoft power bi calculated column count by group
Microsoft power bi calculated column count by group

This is an example of Power BI calculated column count by the group.

Power bi calculated column date from DateTime

Here we will see an example of Power Bi calculated column date from DateTime.

For example, we will use the below table, and we will extract the date from the date-time column in Power Bi.

Power bi calculated column date from DateTime
Power bi calculated column date from DateTime
  • In Power BI Desktop, go to the Modelling tab -> click on the New column from the ribbon.
  • Then write the below formula in the Formula box:
Order Date = FORMAT('Table B'[ Order DateTime], "MM-dd-yyyy")
  • Now you can see a column is added to the table having Date value in Power BI.
calculated column date from DateTime in Microsoft Power bi
calculated column date from DateTime in Microsoft Power bi

This is an example of Power BI calculated column date from DateTime.

Power bi calculated column rankx

Here we will see an example of Power BI calculated column rankx.

For example, we will use the below table, and we will rank the count of the volume.

power bi calculated column rankx
power bi calculated column rankx
  • In Power BI Desktop, go to the Modelling tab-> click on the New column from the ribbon.
  • Then write the below formula in the formula box:
Ranking = RANKX(Binned,Binned[Count of volumne],,DESC,Dense)
  • Now you can see the ranking column added to the table in Power BI.
Microsoft power bi calculated column rankx
Microsoft power bi calculated column rankx

This is an example of power bi calculated column rankx.

You may also like the following Power BI tutorials:

In this Power BI Tutorial, we learned all about Dax calculated columns in Power BI with examples. Examples are listed below:

  • Power bi calculated column
  • Power bi calculated column vs measure
  • Power bi calculated column if
  • Power bi calculated column aggregate
  • Power bi calculated column average
  • Power bi calculated column average by category
  • Power bi calculated column add two columns
  • Power bi calculated column another table
  • Power bi calculated column if and
  • Power bi calculated column moving average
  • Power bi calculated column cumulative sum
  • Power bi calculated column count rows
  • Power bi calculated column count if
  • Power bi calculated column count
  • Power bi calculated distinct column count
  • Power bi calculated column divide
  • Power bi calculated column divided by zero
  • Power bi calculated column contains text
  • Power bi calculated column concatenate
  • Power bi calculated column left
  • Power bi calculated column as legend
  • Power bi calculated column age
  • Power bi calculated column earlier
  • Power bi calculated column empty
  • Power bi calculated column max value
  • Power bi calculated column if an error
  • Power bi calculated column from an unrelated table
  • Power bi calculated column grand total
  • Power bi calculated column in the matrix
  • Power bi calculated column index
  • Power bi calculated column if null
  • Power bi calculated column date format
  • Power bi calculated column dateadd
  • Power bi calculated column days between two dates
  • Power bi calculated column earliest date
  • Power bi calculated column latest date
  • Power bi calculated column last year
  • Power bi calculated column last month
  • Power bi calculated column year
  • Power bi calculated column month name
  • Power bi calculated column week number
  • Power bi calculated column month and year
  • Power bi calculated column previous month
  • Power bi calculated column fiscal year
  • Power bi calculated column today’s date
  • Power bi calculated column YTD
  • Power bi calculated column multiple if statement
  • Power bi calculated column multiply
  • Power bi calculated column number format
  • Power bi calculated column nan
  • Power bi calculated column number to text
  • Power bi calculated column percentage of total
  • Power bi calculated column percentage
  • Power bi calculated column previous row
  • Power bi calculated column decimal places
  • Power bi calculated column sum by group
  • Power bi calculated column switch
  • Power bi calculated column selected value
  • Power bi calculated column sum if
  • Power bi calculated column subtraction
  • Power bi calculated column true false
  • Power bi calculated column time difference
  • Power BI calculated column use relationship
  • Power BI calculated column based on a measure
  • Power bi calculated column based on another column
  • Power bi calculated column between two tables
  • power bi calculated column count by group
  • Power bi calculated column date from DateTime
  • Power bi calculated column rankx
  • Hi Bhawana, I am sincerely grateful for having found these useful examples of calculated column using DAX and reading what you think about knowledge and ignorance. I express my admiration to you and your work.

  • >