• Marek Vavrovic

Mixed Scenarios.

Updated: Sep 28

New Customer Analysis

Calculate running total for selected days.

Calculate running total just for the first 15 days

Partitioning data by month

Count values in one column based on another column in DAX

I want to create a calculated column which will contain the sum of occurrences of values in Column A based on the values in Column B.


Example 1

using EARLIER (as a new column)


Example 2

using FILTER(ALL ()(as a new column)



Calculate SUM for a single item


Salary for Maria = CALCULATE( SUM(DimCustomer[Salary]), FILTER(VALUES(DimCustomer),DimCustomer[Customer]="Maria"))


Partitioning data by month

Calculating average per month.

Step A

calculating sum per month.

I have partitioned data by Year_month and using ALLEXCEPT(FactSales,FactSales[Year_month])) to calculate sum per month.

Step B

calculating number of transaction per month. Partitioning data by Year_month, using ALLEXCEPT(FactSales,FactSales[Year_month])), the same logic as in step A.

Step C

Calculating average by dividing sum of the group by the number of its data points. Data on the chart are sorted by the Index column, which is in the tooltip.


MEAN By Year_Month =

VAR s =

CALCULATE (

SUM ( FactSales[Sales] ),

ALLEXCEPT ( FactSales, FactSales[Year_month] )

)

VAR d =

CALCULATE (

COUNTROWS ( FactSales ),

ALLEXCEPT ( FactSales, FactSales[Year_month] )

)

RETURN

DIVIDE ( s, d, 0 )



Calculate running total just for the first 15 days


FIRST 15 DAYS CUM_SALE =

VAR Period =

DATESBETWEEN ( tblSales[Date], DATE (2021, 01, 01), DATE (2021, 1, 15))

VAR _RESULT =

CALCULATE (

SUM ( tblSales[Sales] ),

DATESYTD ( tblSales[Date], "2021-01-15"),

Period

)

RETURN _RESULT


Calculate running total for selected days.


Step 1

create measure that calculates the running total

Total Sales = CALCULATE(SUM(tblSales[Sales]),DATESMTD(tblSales[Date]))


Step 2

create a new table with selected dates

HelpTable =

CALCULATETABLE (

ADDCOLUMNS (VALUES ( tblSales[Date] ), "Total sales", [Total Sales] ),

tblSales[Date] >= DATE (2021, 01, 03)

&& tblSales[Date] <= DATE (2021, 01, 10))



Step 3

Create a relationship between tblSales and HelpTable.


Step 4

Create a measure with the SUM() function.


New Customer Analysis


This measure calculates the total number of the customers per year. In 2020 there was 6 distinct customer, in 2021, 10 distinct customer. I want to find out the number of the new customers in 2021, means a new customer is every customer having no record in 2020


Number of Customers per year =

CALCULATE (

DISTINCTCOUNT ( FactSales[CustomerID] ),

ALLEXCEPT ( FactSales, FactSales[Date].[Year] ))


Step 1

I want to create a measure which will contain these two tables as a virtual tables.

<all customers> table returns all the customers from 2020-2021.

<2020 Customers> table returns just the customers who have some record in 2021.

Then I use the EXECPT() function to retrieve just those customers who occur only in 2021 table.

Step 2


NEW CUSTOMERS =

VAR ALL_CUSTOMER = VALUES ( FactSales[CustomerID] )

VAR CUSTOMER_PRIOR_YEAR =

CALCULATETABLE (

VALUES ( FactSales[CustomerID] ),

ALL ( FactSales[Date] ),

DATESBETWEEN ( FactSales[Date], DATE (2020, 01, 01), DATE (2020, 12, 31))

)

VAR RESULT =

COUNTROWS (EXCEPT (ALL_CUSTOMER, CUSTOMER_PRIOR_YEAR))

RETURN RESULT

this is how the table looks like when is filtered by 2021 year.


9 views0 comments

Recent Posts

See All

Index

Information functions Filter functions Table manipulation functions Grouping and summarizing Time intelligence functions OPENINGBALANCEYEAR OPENINGBALANCEYEAR(<Expression>,<Dates> [,<Filter>][,<YearEn