Forum Discussion

Helper I

6 years ago

Solved

Calculate weekly volume

Hi,

I have daily volume (Target.Custom) and I want to create a colum that will sum the daily volume by week (Week).

Can someone help me ?

Thanks in advance !

Anonymous
6 years ago
Hi sdlx ,

Add the Index column starting from 1 ( close and apply) and then add this calculated column:

SumOfWeeks =
Var prev=CALCULATE(SUM(Table[Target.Custom]),ALLexcept(Table,Table[Week]))
return
if([Index]>calculate(Min(Table[Index]),filter(all(Table),Table[Week]=earlier(Table[Week]))),blank(),prev)

let me know if this works.

Thanks,
Tejaswi
Anonymous
6 years ago
sdlx

CALCULATE(SUM(Table[Target.Custom]),ALLexcept(Table,Table[Week]),Table[Year]=EARLIER(Table[Year]).

You would need to consider Year as a filter too, because in 2020 you would have the same week number to 2019. You don't want to sum them together.

Paul

4 Replies

Anonymous
Not applicable
6 years ago
Hi sdlx ,

Add the Index column starting from 1 ( close and apply) and then add this calculated column:

SumOfWeeks =
Var prev=CALCULATE(SUM(Table[Target.Custom]),ALLexcept(Table,Table[Week]))
return
if([Index]>calculate(Min(Table[Index]),filter(all(Table),Table[Week]=earlier(Table[Week]))),blank(),prev)

let me know if this works.

Thanks,
Tejaswi
- sdlx
  Helper I
  6 years ago
  Hi !
  
  Thank you for your quick response.
  Anonymous I tried your solution and I get this error message:
  
  then I tried to add your filter Anonymous :
  
  And I get this message;
  
  Do you understand my mistake ?
  
  thanks for your help !
  - Anonymous
    Not applicable
    6 years ago
    HI sdlx ,
    
    Loks like you are trying to use it a a Measure.
    
    Use this formula in a Calculated Column and you should be good to go,.
    
    Thanks,
    Tejaswi
Anonymous
Not applicable
6 years ago
sdlx

CALCULATE(SUM(Table[Target.Custom]),ALLexcept(Table,Table[Week]),Table[Year]=EARLIER(Table[Year]).

You would need to consider Year as a filter too, because in 2020 you would have the same week number to 2019. You don't want to sum them together.

Paul

Related content