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