Rolling Period Calculation - DATESINPERIOD

 Sometimes , we have to calculate total amount for rolling 12 month/rolling 3 month.

For such rolling period calculations, we can use Time intelligence function "DATESINPERIOD"

Rolling 3 month Sales =

VAR vMaxSalesDate = MAX(Sales[OrderDate])

RETURN

CALCULATE([Sales Amount], DATESINPERIOD(dim_date[Date], vMaxSalesDate, -3,MONTH))

Calculate customers whose sales are above average customer sales.

 First we need Total Sales Amount 

Sales Amount = SUM(Sales[SalesAmount])

Then we need to calculate Average Sales Amount

If we use below formula, it will give us the average. But we want to compare this average with sales amount of each customer. This average will change for each row as it operates on row context

DIVIDE([Sales Amount], DISTINCTCOUNT(Customers[CustomerName]), 0)

Hence we use below formula:

Avg Sales = AVERAGEX(ALL(Customers[CustomerName]), [Sales Amount])

Now, we need a flag which states if the Sales Amount is greater than Avg Sales

Above Avg Customer = IF([Sales Amount] > [Avg Sales], 1, 0)

In the table visual we can display CustomerName, SalesAmount. In Visual Filter we can add "Above Avg Customer = 1" and apply filter

ISINSCOPE()

HASONEVALUE

 Say there are scenarios where we want to link table & graph.

 Depending on the values of table the graph value also should   change.

Say in a table we have product category: Bike , cars, scooter

We want to show Bike Sales when Bike is selected in the table, when car is selected the graph should show sales of graph.

When all product category is selected, by default the chart should show car.

Then we can write an expression like 

IF (HASONEVALUE('Product'[Category]), 'Product'[Category], [Bike Sales])

Selected Category = IF(HASONEVALUE('Product'[Category]), 'Product'[Category], "Bike"

Other scenario is when we want to show multiple selected values

selectedyear =

 IF

 (HASONEVALUE(Fiscal_year[FY_desc]),

 SELECTEDVALUE(Fiscal_year[FY_desc]),

 CONCATENATEX(Fiscal_year, Fiscal_year[FY_desc], ",")

 )

 

Running Total/Cumulative Total in DAX

 Say we have below data:

Now if we want to calculate the running total, something like below

Through DAX, we can write below measure:

Cumulative Amt =

CALCULATE

(

    SUM(table_orders[Bill_amt]),

    FILTER

    (all(table_orders),

    table_orders[OrderDate] <= MAX(table_orders[OrderDate]

    )

    )

    )

But there is a catch here.

Say we have HotelName as slicer then cumulative total would not be just for that hotel.

We can modify the DAX measure as below:

Cumulative Amt slicer filter =

CALCULATE

(

    SUM(table_orders[Bill_amt]),

     FILTER(ALLSELECTED(table_orders), table_orders[OrderDate] <= MAX(table_orders[OrderDate]))

)

Ideally if some HotelName filter is applied then we need cumulative total for just the hotel name.

SQL query:

select OrderID ,
OrderDate,	
HotelName,
FoodItem,
Bill_amt,
sum(Bill_amt) over (order by OrderDate) 
from Orders

selectedvalue || HasOneValue || Values || CONCATENATEX

 SELECTEDVALUE : Displays the value that is selected. If multiple values are selected it displays blank

selectedvalue2 = SELECTEDVALUE(dim_date[Year])

HASONEVALUE = It returns true/false. Can be used inside IF function.

Say, we want to display the selected year and if multiple values are selected display them as concatenated values.

SelectedYear = IF

(HASONEVALUE(dim_date[Year]), SELECTEDVALUE(dim_date[Year]), CONCATENATEX(VALUES(dim_date[Year]), dim_date[Year], ","))

CONCATENATEX : displays all values inside the parameters  with delimiters that was specified.

If we observe we have used VALUES function as well.

VALUES : It returns single column with unique values.

If we don't use VALUES then it returns values like 2024, 2025, 2024, 2025,2024, 2025,2024, 2025,2024, 2025,2024, 2025,2024, 2025,2024, 2025,2024, 2025.

Basically for every row in dim_date it concatenates values. To avoide that we use VALUES function since it returns unqiue values.

union

ALLNOBLANKROWS

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

DAX Functions

 USERELATIONSHIP

generates table

-----------------------------------------------

DATATABLE

UNION

SUMMARIZE

DISTINCT

FILTER 

GENERATESERIES

CALENDAR

CALENDARAUTO

--------------------------

Aggregate Functions:

SUM

MIN

MAX

AVERAGE

COUNTROWS

DISTINCTCOUNT

-----------------------------------

Information Functions:

ISBLANK

ISFILTERED

ISCROSSFILTERED

----------------------------------------

Logical Functions

IF

AND

SWITCH

------------------------------------------

Date & Time Functions

DATEDIFF

DATEVALUE

-------------------------------------------

FILTER Functions

CALCULATE

FILTER

VALUE

ALL

TOPN