cancel
Showing results for
Did you mean:  Helper I

## Daily Total for period analysis

Hi all,

I have a table of customer Buys and Sells. I calculate the Daily Ownership Change by subtracting these two.

Also, I have a measure to check if Daily Ownership Change was under threshold (25%).

More specifically, from this table

 Date Customer Type Items 1/1/2021 A Buy 100 1/1/2021 A Buy 100 1/1/2021 A Sell 500 1/1/2021 A Buy 300 8/1/2021 B Buy 100 8/1/2021 B Sell 50 9/1/2021 A Sell 100 9/1/2021 A Buy 80

I then create the table visual

 Date Customer Daily Ownership Change Daily Ownership Change PCT isUnchagned 1/1/2021 A 0 (ABS(100 + 100 -500 + 300)) 0% (divide (Daily Ownership Change, TotalItems) -> 0/1000 1 (if Daily Ownership Change PCT < 0.25, 1, 0) 8/1/2021 B 50 33.3%  (50/150) 0 9/1/2021 A 20 11% (20/180) 1

What I want now is to create a Period Report showing how many days the isUnchanged was 1,

Lets say for 1/1/2021 - 10/1/2021

 Customer isUnchanged Total A 2 B 0

When I create the latter visual, I get zero isUnchanged Total for all customers.

Any ideas??

Kyriakos

1 ACCEPTED SOLUTION  Community Support

Hi  @KyriakosT  ,

Here are the steps you can follow：

1. Create measure.

``````Daily Ownership Change =
VAR _2=CALCULATE(SUM('Table'[Items]),FILTER(ALL('Table'),'Table'[Date]=MAX('Table'[Date])&&'Table'[Customer]=MAX('Table'[Customer])&&'Table'[Type]="Sell"))
return
ABS(_1-_2)``````
``````Daily Ownership Change PCT =
var _1=CALCULATE(SUM('Table'[Items]),FILTER(ALL('Table'),'Table'[Date]=MAX('Table'[Date])))
return
DIVIDE('Table'[Daily Ownership Change],_1)``````
``````isUnchagned =
IF('Table'[Daily Ownership Change PCT]<0.25,1,0)``````

Result: 2. Put only the result of [Customer]: This is because Measure will only perform calculations based on the fields you place. There is only [Customer] in this table, and calculations will be grouped by [Customer] instead of [Date].

3. You can use measure to create an identical virtual table.

``````isUnchanged Total =
var _1=
SUMMARIZE('Table','Table'[Date],'Table'[Customer]," Daily Ownership Change",[Daily Ownership Change],"2",[Daily Ownership Change PCT],"3",[isUnchagned])
var _2=COUNTAX(FILTER(_1,=1),[Customer])
return
IF(_2=0,0,_2)``````

4. Result: Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 REPLIES 4  Community Support

Hi  @KyriakosT  ,

Here are the steps you can follow：

1. Create measure.

``````Daily Ownership Change =
VAR _2=CALCULATE(SUM('Table'[Items]),FILTER(ALL('Table'),'Table'[Date]=MAX('Table'[Date])&&'Table'[Customer]=MAX('Table'[Customer])&&'Table'[Type]="Sell"))
return
ABS(_1-_2)``````
``````Daily Ownership Change PCT =
var _1=CALCULATE(SUM('Table'[Items]),FILTER(ALL('Table'),'Table'[Date]=MAX('Table'[Date])))
return
DIVIDE('Table'[Daily Ownership Change],_1)``````
``````isUnchagned =
IF('Table'[Daily Ownership Change PCT]<0.25,1,0)``````

Result: 2. Put only the result of [Customer]: This is because Measure will only perform calculations based on the fields you place. There is only [Customer] in this table, and calculations will be grouped by [Customer] instead of [Date].

3. You can use measure to create an identical virtual table.

``````isUnchanged Total =
var _1=
SUMMARIZE('Table','Table'[Date],'Table'[Customer]," Daily Ownership Change",[Daily Ownership Change],"2",[Daily Ownership Change PCT],"3",[isUnchagned])
var _2=COUNTAX(FILTER(_1,=1),[Customer])
return
IF(_2=0,0,_2)``````

4. Result: Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.  Solution Sage

[Total IsUnchanged] = SUM( 'Table'[IsUnchanged] )

What I'm saying above is that you could (maybe even should) create the second table (you call it visual) in Power Query in full, meaning for all customers and all dates. If you do, then it'll be easier and MUCH faster to calculate other things. Basically, the rule is this: You should pre-calculate everything that you can in Power Query. Calculated columns should be calculated in Power Query unless there is absolutely no other way than to do this with DAX.

There are many reasons behind the above but to explain all this would take a full article or a chapter in a book.  Solution Sage

The first calculation should be performed in Power Query (by adding the 3 columns). Then, back in Power BI Desktop you can easily create the measure by just summing up the values in the last column.

By the way, you should create a measure for the Total, not use the automatic summation feature of PBI Desktop. Do you have such a measure? What does it look like?  Helper I

I didn't quite get the first part where you mention Power Query.

I have the following measures:

Sells= CALCULATE(SUM(Items), 'Table'[Type] = "Sell"
Total = CALCULATE(SUM(items))
Daily Ownership Change PCT = Daily Onwership Change/Total (%)

Then I calculate
isUnchanged = if (Daily Ownership Change PCT < 0.25, 1, 0)

All of these are working if is slice customer by date (see visual 2).

But as I understand, since my measures are mising the daily factor (it is automatically fixed by table visual),
I cannot use them directly in my periodic analysis (visual 3).

I need to specify that my measures refer to daily basis and I don't know how.

Do you have any suggestion?  