Week on week variation agragation

[scbi]

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

[v-yuta-msft]

@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

[scbi]

Hi
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

[scbi]

Hi,
Is it more clear ?