- Marek Vavrovic

# Mixed Scenarios.

Updated: Sep 28

__Calculate running total for selected days.__

__Calculate running total just for the first 15 days __

__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__

__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 __

__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.__

__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.