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 that _MaxWeeklyImpressions should give Sum of All Weekly Impressions . 

 

The value against Each individual date of W46 2017  should be 43,795 ( 8003+7792+7891+7473+6808+5282)

 

Calculation I am using is : 

 

_MaxWeeklyImpressions = CALCULATE(
SUM(ImpressionShare[Impressions]),
ALLEXCEPT('Date','Date'[FW Week Year])
)
 
 

 

Any Idea what I am doing wrong here ? Or how It can be achieved ? 

 

Thanks

 

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] )
)

     

5 Replies

  • v-frfei-msft's avatar
    v-frfei-msft
    Community Support
    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] )
)

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi digitalau ,

     

    First, make sure you are using a Calculated Measure for _MaxWeeklyImpressions.

     

    If you already are, then it is probably that ALLEXCEPT is not working  because you are refering to another table.


    I would suggest to bring your Week column into the same table as Impressions, as such:


    Create new calculated column in ImpressionShare Table:

    NEW WEEK COLUMN = LASTNONBLANK( 'Date'[FW Week Year] , 0 )

    Then Update your _MaxWeeklyImpressions measure , as such:

    _MaxWeeklyImpressions = CALCULATE(
SUM(ImpressionShare[Impressions]),
ALLEXCEPT('ImpressionShare','ImpressionShare'[NEW WEEK COLUMN)
)

    Let me know if anything is unclear.
    Cheers,
    Rob

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • 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 )