Forum Discussion

sw123's avatar
sw123
Icon for Helper III rankHelper III
2 years ago

Running total kg per week

Hi,

 

I have a table that looks like this:

 

IDSiteWeekDateTotal kgTotal kg siteTotal kg week
260A426/01/2024978,023 06119 045
259A426/01/2024980,022 08318 067
258A426/01/20241 019,021 10317 087
257A425/01/2024858,620 08416 068
256A425/01/20241 035,019 22515 209
255A425/01/20242 057,018 19014 174
254A424/01/20241 040,016 13312 117
253A424/01/20241 055,015 09311 077
252A424/01/20242 053,014 03810 022
251A423/01/20241 020,011 9857 969
250A423/01/20241 032,010 9656 949
249A423/01/20241 750,09 9335 917
248A422/01/20241 035,08 1834 167
247A422/01/20243 132,07 148 
246A321/01/20241 928,04 016 
245A320/01/20242 088,02 088 

 

I would like the column Total kg week to calculate how much the toal kg:s are per week as the week goes by, as in the example is calculated for week 4. And for the Total kg site to calculate total kg:s for the site as time goes by. Of course there will be other sites too in the table. Is this possible to do in Power BI?

 

Grateful for answers.

 

 

Need Help

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    HI sw123,

    What version of weekly total are you wanted? The dynamic rolling 7 days or aggregated records based on current week number?

    Here are sample measure formulas of rolling 7 days and weekly total based on current site group:

    Rolling 7day =
VAR currDate =
    MAX ( Table1[Date] )
RETURN
    CALCULATE (
        SUM ( Table1[Total kg] ),
        FILTER ( ALLSELECTED ( Table1 ), [Date] >= currDate - 7 ),
        VALUES ( Table1[Site] )
    )

Total per week =
VAR currDate =
    MAX ( Table1[Date] )
RETURN
    CALCULATE (
        SUM ( Table1[Total kg] ),
        FILTER (
            ALLSELECTED ( Table1 ),
            YEAR ( [Date] ) = YEAR ( currDate )
                && WEEKNUM ( [Date] ) = WEEKNUM ( currDate )
        ),
        VALUES ( Table1[Site] )
    )

    Regards,

    Xiaoxin Sheng

    • sw123's avatar
      sw123
      Icon for Helper III rankHelper III
      2 years ago

      Hi, I want to have it based on the week number, like running totals for week 1, new running totals for week 2 and so on.

       

      Thank you for the formulas. I will try these.

    • sw123's avatar
      sw123
      Icon for Helper III rankHelper III
      2 years ago

      Hi,

       

      I tried the Total per week formula, but it gave me the same value as in column Total kg? 

       

      Can you tell me what is wrong?

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi sw123 ,

        Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

        How to Get Your Question Answered Quickly  

        Notice: please not attach any sensitive data in sample.

        Regards,

        Xiaoxin Sheng