Forum Discussion

digitalau's avatar
digitalau
Frequent Visitor
6 years ago
Solved

Power BI Calculating Weekly Sum

I am trying to find Weekly Sum in a table , I have a pretty extensive Date table which uniquely identifies a Week. However, it's not working and I am not able to understand why .    What I want is ...
Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    6 years ago

    Hi digitalau ,

     

    To use VALUES function should work.

     

    Measure = 
CALCULATE (
    SUM ( 'Table'[values] ),
    FILTER ( ALL ( 'Table' ), 'Table'[date] <= MAX ( 'Table'[date] ) ),
    VALUES ( 'Table'[Wn] ),
    VALUES ( 'Table'[date].[Year] )
)

     

digitalau's avatar
digitalau
Frequent Visitor
6 years ago

Robin, 

 

Thank you for the reply. It is a measure already. 

 

It looks like ALLEXCEPT is not working . 

 

They're coming from different tables  linked together with Date Column. ( 1 to Many  from Date to ImpressionShare)

 

The reason I would not want to bring Impressions to Date table is that I am going to have "Date" connections with 12 tables and for several other columns. 

 

I am trying to understand the logic of why it's not working . 

 

 

There's a 1 to many link between Date and Impression table 

 

 

digitalau's avatar
digitalau
Frequent Visitor
6 years ago

I tried using the method you mentioned . 

 

1. Created a column in ImpressionShare Table : _Week4Impressions , which gives me a week entry in ImpressionShare column

2. Created _SumWeeklyImpressions :  

_SUMWeeklyImpressions = CALCULATE(
SUM(ImpressionShare[Impressions]),
ALLEXCEPT('ImpressionShare','ImpressionShare'[_Week4Impressions]
)
 
However, now it sums up the whole table . ( no aggregation yet to week level ) 
 
  • ERIC_A's avatar
    ERIC_A
    Helper II
    3 years ago

    hi in case you need it. this is what I did to get my total per week 

    weeklysum = calculate(count(Table1[calls]),ALLEXCEPT(Table1,Table1[Creation Week#]))