RelatedTable function

FILTER function - count of orders whose bill amount is greater than avg bill amount

ALLEXCEPT

Calculating %age in Power BI - ALL function

 Say we have below table :

Now if we want to calculate the %age of expenses, we need the total expense.

Then say we want to calculate %age total expense for Transportation, then we can use below formula:

=5000/47500 = 0.105 = 10.5%

Now to calculate the total expense in DAX, we need to use "ALL" function

Total Expenditure(ALL) = CALCULATE([TotalExpenditure] , ALL(Sheet1[Category]))

Then %age can be calculated as below:

% Total Expenditure = DIVIDE([TotalExpenditure], [Total Expenditure(ALL)], 0)

Difference Between DAx functions : Date & DateValue

 DAX function Date requires 3 parameters as number

Date(year, month, day)

Example: Date(2022, 2, 17)

This will give date as "2022-02-17"

DateValue converts a text string to date time

Example: we have year in one column and month name as "feb" in another column.

And we want to combine year & month to date column, we can write as below

 DATEVALUE("1-"&fact_sales_Iphone14[month]&"-"&fact_sales_Iphone14[Year])

Create Date table in Power BI

 Date table can be created in power bi in multiple ways:

Using the CALENDAR DAX function:

In Power BI, click New table.

Name the query, example it can be dim_date or just date or date_dim

dim_date = CALENDAR(DATE(2017, 01, 01), DATE(2027-12-31))

This will create dim_table with column "date" having dates ranging from 1-jan-2017 to 31-dec-2027

Tricky part to remember: Parameters inside CALENDAR has to be date. We just can't hard-code '2017-01-01' and '20127-12-31'

Thats the reason we use DATE function 

CALENDARAUTO DAX function:

CALENDARAUTO() is a function that scans all datetime columns in the model. Picks the min & max dates from those columns and returns contiguos dates as table with "Date" column.

Optionally we can pass number 1 to 12 to the function which is considered as the end month of fiscal year

Creating Calendar Date using M Langauage/PowerQuery

Open Power Query, take the blank query and type below M code:

{Number.From(#date(2017,9,1))..Number.From(#date(2022,12,31))}

Convert the list to table and change the data type to date. 

Time Intelligence Functions

 TOTALYTD

TOTALQTD

TOTALMTD

DATESYTD

DATESMTD

DATESQTD

DATEADD

SAMEPERIODLASTYEAR

PARALLELPERIOD