Power BI useful Formulas

Power BI Aggregate functions


Measure Name = SUM(Expression or Column Name)

Measure Name = AVERAGE(Expression or Column Name)

Measure Name = MIN(Expression or Column Name)

Measure Name = MAX(Expression or Column Name)

MaxSale = MAX(Expression or Column Name)

Measure Name = COUNT(Expression or Column Name)

Measure Name = VAR.S(Expression or Column Name)

Measure Name = VAR.P(Expression or Column Name)

Measure Name = STDEV.P(Expression or Column Name)

Measure Name = STDEV.S(Expression or Column Name)

Measure Name = MAX(Expression1, Expression2)                             --2 args

Measure Name = MIN(Expression1, Expression2)                             --2 args




Create a new table from existing table

1.       Table_Name = related(Existing_TableName)

2.       NewTableName = SUMMARIZE(ExistingTableName, ExistingTableName[column1], ExistingTableName[column2])                   --selected columns



Power BI DATE functions

1.       Day= DAY(NewTableName[OrderDate])                                                 – day from date column

2.       MonthinDate = MONTH(EmployeeSales[HireDate])

3.       YearInDt = YEAR(EmployeeSales[HireDate])

4.       Hour = HOUR(EmployeeSales[HireDate])

5.       Min = MINUTE(EmployeeSales[HireDate])

6.       Sec = SECOND(EmployeeSales[HireDate])

7.       Date1 = DATE(YEAR(NewTableName[Date]), MONTH(NewTableName[Date]),                 

8.       DAY(NewTableName[Date]))

9.       WeekDay = WEEKDAY(NewTableName[OrderDate], 1)    --If Number = 1 Week begins on Sunday, and If Number = 2 then Week begins on Monday. Let me create a column to find the week number

10.   WeekNum = WEEKNUM(EmployeeSales[HireDate], 1)

11.   YearFrac = YEARFRAC(EmployeeSales[HireDate], NOW())

12.   DateInMonth = FORMAT(NewTableName[OrderDate],"mmmm")

Let me add 2 months to Hire Date on Existing month

13.   Edate = EDATE(EmployeeSales[HireDate], 2)        --adding 2 months

14.   Eomonth = EOMONTH(EmployeeSales[HireDate], 3)         --adding 3 months

15.   DATEDIFF(Start_Date, End_Date, Difference_Value)         ---dateDiff

16.   DateDiff = DATEDIFF(EmployeeSales[HireDate], NOW(), YEAR)    –dateDiff in year

17.   Calender = CALENDAR(DATE(2018,1, 1), DATE(2018, 1, 15))           –creating a date table from a given range

18.   CalenderAutotable = CALENDARAUTO()                 –will create a new date table and will take auto date from existing table



Power BI Logical functions

1.       IfElse = IF(NewTableName[WeekDay]=23,"Yes","No")

2.       Column = IF(NewTableName[IFLeng]=2,FORMAT(NewTableName[IFLeng],"00"))                 -- if IFLeng=2, then add one preceding zero.

3.       Column 2 = IF(NewTableName[WeekDay]>20,"Greater","Smaller")



4.       NestedIfEx = IF(NewTableName [WeekDay] < 30, "Lesser than 30",       IF(NewTableName [WeekDay] > 30, "Good", "Greater than 30" ))



5.       IFAND = IF(AND(NewTableName[WeekDay]>20,NewTableName[WeekDay]<30),"Between Thirty","Not in 20-30")



6.        IFOR = IF(OR(NewTableName[WeekDay]=30,NewTableName[WeekDay]=20),"Thirty-Twenty")



7.       Number_Of_Rows = COUNTROWS('Sales Data') – Your table_name.

8.       IfNot = NOT(IF(NewTableName[WeekDay]>30,"True","False"))



9.       SwitchMonth = SWITCH(MONTH(NewTableName[OrderDate]), 1, "January", 2, "February", 3, "March", 4, "April", 5, "May",12, "December", "Unknown")  --If jan,feb or other months are not matching with their respective serial number , will return Unknown else same month name.