Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
fsalinass
Frequent Visitor

Sum acumulated

Hi!
I have a question a little complex, I have a base for weeks (can not have for days) of 3 full years, each week has assigned a number of days (for example the week 51 is assigned with 52304 days), I need for each week to sum a year to the back, that is, for week 51 sum from week 52 of 2017 to week 51 of 2018, and so on for all the weeks of the last 2 years. Any ideas?

4 REPLIES 4
Greg_Deckler
Super User
Super User

Sample data would really assist here along with your expected output. Probably can get there with some variation on my Time Intelligence The Hard Way Quick Measure, but would need to see how your data is laid out. So, for example, do your week numbers range from 0-53 and you have a year column as well or are they assigned 0-150(ish)? What do the assigned number of days mean, is that cumulative or something?


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Hi!

 

The number days is not acumulative, is like a random number for eachs week.

 

Here is a data example:

 

Year   Month   Week   days   Sede

2017      7          42      254      a

2017      7          42      2          b

2018      8          44      13        c

2016      7          42      15        a

2016      8          44      24        a

2018 7 43 0 b

 

So, for each week i have to sum the Days of the 52 before week for all the "Sedes".

For week 41 2018 i need to sum the days from week 42 2017 to week 41 2028

For week 35 2018 i need to sum the days from week 36 2017 to week 35 2028

HI @fsalinass,

 

I write a measure to calculate total 'daysofweeks' based on current year, week and grouped by sede, maybe you can try it if it works.

Measure =
VAR currYear =
    MAX ( Table1[Year_dp] )
VAR currWeek =
    MAX ( Table1[Week-DP] )
RETURN
    CALCULATE (
        SUM ( Table1[daysbyweek] ),
        FILTER (
            ALLSELECTED ( Table1 ),
            OR (
                [Year_dp] = currYear
                    && [Week-DP] <= currWeek,
                [Year_dp]
                    = currYear - 1
                    && [Week-DP]
                    > currWeek - 1
            )
        ),
        VALUES ( Table1[Sede] )
    )


If above not help, please share more detail information about your requirement with expected results.

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

The days is like a random data, and is not acumulative.

 

Here is a sample data:

 

Year_dpMonth_dpWeek-DPdaysbyweekSede
20173130a
201731312b
20173140c
201731220d
201731019d
20173110b
20173140a
201731313b
20173100c
20173130d
201731449d
201731025b
201731325a
20173140b
20173111c
20173110d
20173130d
20173120b
201731424a
20173110b
20173110a
20173120b
20173120c
20173110d
201731314d
20173110b
20173100a
20173120b
20173100c
20173130d
20173130d
20173100a
20173120b
20173110c
20173100d
20173140d
201731336b
20173140a
20173110b
20173100c
20173130d
20173120d
20173110a
201731412b
20173120c
20173110d
20173120d
20173110b
20173140a
20173120b
20173100c
20173110d
20173130d
201731329a
20173120b
20173100c
20173130d
20173110d
20173110b
20173140a
20173120b
2017312165c
20173100d
20173100d
20173100a
20173140b
20173120c
20173130d
20173120d

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.