Forum Discussion
scbi
7 years agoNew Member
Week on week variation agragation
Good morning all,
I have a Measure that computes week on week variation in a dataset.
Structure of the dataset is rather simple. one table : 'Database' and a few columns :
ValuationDate, Name, Region, Cheap and my measure : WoW Cheap
I have multiple names & valuationdate.
My measure works perfectly when I display in a table : Names and WoW Cheap
I have a probleme though when I want to display an average by Region (table with Region and WoW Cheap)
I think it is linked to my "selection" or filters and tried with "allselected" or "allexcept" but it does not work...
here is my code :
WoW Cheap =
VAR current_Product =LASTNONBLANK ( 'DataBase'[Name]; [Name] )
VAR current_Date = MAX ( 'DataBase'[ValuationDate] )
VAR current_avg =
AVERAGEX (
FILTER (
ALL ( 'DataBase' );
[Name] = current_Product
&& [ValuationDate] = current_Date
);
[Cheap]
)
VAR previous_avg =
AVERAGEX (
FILTER (
ALL ( 'DataBase' );
[Name] = current_Product
&& [ValuationDate]
= MAXX (
FILTER (
ALL ( 'DataBase' );
[Name] = current_Product
&& [ValuationDate] < (current_Date-6)
);
[ValuationDate]
)
);
[Cheap]
)
RETURN
IF ( previous_avg <> 0; current_avg-previous_avg; 0 )
Many thanks for your help in advance !
Pierre
I have a Measure that computes week on week variation in a dataset.
Structure of the dataset is rather simple. one table : 'Database' and a few columns :
ValuationDate, Name, Region, Cheap and my measure : WoW Cheap
I have multiple names & valuationdate.
My measure works perfectly when I display in a table : Names and WoW Cheap
I have a probleme though when I want to display an average by Region (table with Region and WoW Cheap)
I think it is linked to my "selection" or filters and tried with "allselected" or "allexcept" but it does not work...
here is my code :
WoW Cheap =
VAR current_Product =LASTNONBLANK ( 'DataBase'[Name]; [Name] )
VAR current_Date = MAX ( 'DataBase'[ValuationDate] )
VAR current_avg =
AVERAGEX (
FILTER (
ALL ( 'DataBase' );
[Name] = current_Product
&& [ValuationDate] = current_Date
);
[Cheap]
)
VAR previous_avg =
AVERAGEX (
FILTER (
ALL ( 'DataBase' );
[Name] = current_Product
&& [ValuationDate]
= MAXX (
FILTER (
ALL ( 'DataBase' );
[Name] = current_Product
&& [ValuationDate] < (current_Date-6)
);
[ValuationDate]
)
);
[Cheap]
)
RETURN
IF ( previous_avg <> 0; current_avg-previous_avg; 0 )
Many thanks for your help in advance !
Pierre
3 Replies
- v-yuta-msft
Community Support
scbi ,
Your description is not so clear. Could you please share some sample data or post the table structure/relationship for further analysis?
Regards,
Jimmy Tao
- scbiNew MemberHi
Unfortunately I am not allowed to share data sample...
Here is an example instead
ValuationDate, Name, Region, Cheap
01/01/19 , A, Europe, 3
01/01/19 , B, US, 2.5
01/01/19, C , Europe, 4
01/01/19, D, US, 0
.......
02/01/19 , A, Europe, 2.4
02/01/19 , B, US, 1
02/01/19, C , Europe, 6
02/01/19, D, US, 2
....
Etc
I have 1 year history and around 450 unique names.
No relationship.
Thanks- scbiNew MemberHi,
Is it more clear ?