Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

current week data sum calculation based on Variable Value

Hi Guru's

 

I have data excel like attached and i want to display sum based on the current week variable

 

Current Week = WEEKNUM ( TODAY (), 2 )-1----For example this variable output is 10

 

iwant to pick slaes_10,Margin_10,Profit_10 columns as Report output.

 

Next week the output may get changed to Pick   slaes_11,Margin_11,Profit_11 columns as Report output

 

 

 

Thanks,

Joshi

 

 

Need Help
  • v-danhe-msft's avatar
    v-danhe-msft
    7 years ago

    Hi Anonymous ,

    Based on my test, you could refer to below steps:

    Unpivot other columns in query editor:

    Result:

    Apply it and create below measure:

    Measure = CALCULATE(SUM(Table1[Value]),FILTER('Table1','Table1'[Attribute]=CONCATENATE("Sales_",WEEKNUM ( TODAY (), 2 )-1)))

    Set filter and you could see the correct result:

    You could also download the pbix file to have a view.

     

    Regards,

    Daniel He

     

1 Reply

  • v-danhe-msft's avatar
    v-danhe-msft
    Microsoft Employee
    7 years ago

    Hi Anonymous ,

    Based on my test, you could refer to below steps:

    Unpivot other columns in query editor:

    Result:

    Apply it and create below measure:

    Measure = CALCULATE(SUM(Table1[Value]),FILTER('Table1','Table1'[Attribute]=CONCATENATE("Sales_",WEEKNUM ( TODAY (), 2 )-1)))

    Set filter and you could see the correct result:

    You could also download the pbix file to have a view.

     

    Regards,

    Daniel He