Forum Discussion

scbi's avatar
scbi
New Member
7 years ago

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

3 Replies

  • v-yuta-msft's avatar
    v-yuta-msft
    Icon for Community Support rankCommunity 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

    • scbi's avatar
      scbi
      New Member
      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