Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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 CustomerType Items
1/1/2021 ABuy 100
1/1/2021 ABuy 100
1/1/2021 ASell 500
1/1/2021 ABuy 300
8/1/2021 BBuy 100
8/1/2021 BSell 50
9/1/2021 ASell 100
9/1/2021 ABuy 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??

 

Thank you in advance,

 

Kyriakos

1 ACCEPTED SOLUTION
v-yangliu-msft
Community Support
Community Support

Hi  @Anonymous  ,

Here are the steps you can follow:

1. Create measure.

Daily Ownership Change =
var _1=CALCULATE(SUM('Table'[Items]),FILTER(ALL('Table'),'Table'[Date]=MAX('Table'[Date])&&'Table'[Customer]=MAX('Table'[Customer])&&'Table'[Type]="Buy"))
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:

v-yangliu-msft_0-1620868338529.png

2. Put only the result of [Customer]:

v-yangliu-msft_1-1620868338540.png

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,[3]=1),[Customer])
return
IF(_2=0,0,_2)

4. Result:

v-yangliu-msft_2-1620868338544.png

 

 

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.

View solution in original post

4 REPLIES 4
v-yangliu-msft
Community Support
Community Support

Hi  @Anonymous  ,

Here are the steps you can follow:

1. Create measure.

Daily Ownership Change =
var _1=CALCULATE(SUM('Table'[Items]),FILTER(ALL('Table'),'Table'[Date]=MAX('Table'[Date])&&'Table'[Customer]=MAX('Table'[Customer])&&'Table'[Type]="Buy"))
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:

v-yangliu-msft_0-1620868338529.png

2. Put only the result of [Customer]:

v-yangliu-msft_1-1620868338540.png

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,[3]=1),[Customer])
return
IF(_2=0,0,_2)

4. Result:

v-yangliu-msft_2-1620868338544.png

 

 

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.

Anonymous
Not applicable

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

Anonymous
Not applicable

@Anonymous 

 

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?

Anonymous
Not applicable

@Anonymous  thanks for your response.

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

I have the following measures:

Buys = CALCULATE(SUM(Items), 'Table'[Type] = "Buy"

Sells= CALCULATE(SUM(Items), 'Table'[Type] = "Sell"
Total = CALCULATE(SUM(items))
Daily Onwership Change = ABS(Buys-Sells)
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? 
 
 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.