Show Sum of previous Weeks in Line Chart

[Micha3lS]

Hello I try to recreate the follwing chart:

I Have a table that summarizes the values for the current time: So for the current week there is a backlog of x. 

Is it possible to sum up all previous valuse of backlog for all previous weeks and sum the up to get only one bar for weeks < 7?

There are Values for the backlog back to calendar week 28 in the previous year, and i dont want to display all of them in seperate bars.

Thanks or your help.

Michael

[johnt75]

You'll need a column on your Date table to identify the weeks. I usually use Week Commencing, but week number will do just as well.

Backlog Measure =
var startOfCurrentWeek = LOOKUPVALUE( 'Date'[W / C], 'Date'[Date], TODAY() )
var maxDate = MAX( 'Date'[Date] )
var result = IF ( maxDate >= startOfCurrentWeek,
    SUM( Table[Backlog Column] ),
    CALCULATE(
        SUM( Table[Backlog Column] ),
        REMOVEFULTERS( 'Date'[Date] ),
        'Date'[Date] < startOfCurrentWeek
    )
return result

[Micha3lS]

This means, that I have to put the value for the prev weeks into a 2nd chart, that only displays the values for the previous weeks?

[johnt75]

No, it should display all prior weeks in just one value. I think you may need to set the date filter on the visual to be a relative date filter, just show the last 2 calendar weeks including today.

If you need to show future values as well you'd need to add another column onto the Date table that you could use as a filter, using whatever logic you choose, e.g. return 1 if the date is after the start of last week and within the next 6 months.

[Micha3lS]

Ok, this would work, if current week ist the actual current week. 

But the "curent" week is a selected week from a slicer. Sorry I didnt make that clear enough.

Each dataset for each week will be marked by a column, to which week it belongs to.

[johnt75]

If its from a slicer you should be able to replace the LOOKUPVALUE with SELECTEDVALUE on the appropriate column. The rest of it can stay the same.

[Micha3lS]

I just came back to the topic because I had to deal with some other stuff in the meantime.

Unfortunately I can't make your solution work on my current setup. To add some clarity I will post you some screenshots:

KW is the calenderweek im filtering on with the slicer (I need to add a slicer and a column for the year too), also the column Werk is filtered on by a slicer. The KalenderwocheEndterminEck is the week the product is due. I added the calculated columns backlog and open amount. If the due week is < KW it's backlog, if bigger it's open amount.

In my chart i now want to Sum up the backlock, and the delivered Amount (gelieferte Menge) in one column for all weeks previous the current selected week. The next bar will be the Backlog and delivered amount for the current selected week. After that the following bars should display the open amount for the next weeks.

I tried something in the lines of:
 

Delivered = 

VAR _currentweek = SELECTEDVALUE(DateKey[KW])

RETURN
SUMX(
    DeliveryPerf,
    IF(DeliveryPerf[KW]>DeliveryPerf[KalenderwocheEndterminEck],
        Calculate(
            Sum(DeliveryPerf[GelieferteMenge]),
            REMOVEFILTERS(DateKey),
            DateKey[KW] < _currentweek
            ),
        Sum(DeliveryPerf[GelieferteMenge])
        )
    )

but that didn't work.

I would appreciate some help and thanks in advance!

[Micha3lS]

@amitchandak

Unfortunately I don't rly understand how that is supposed to work.

I forgot to mention that the current week is selected via a slicer on the page.

So the x axis is the calendar week, and the y axis is the amount (consisting of 4 values). Everthing that is past the current week should be summarized into one column.

The easiest case would look like this:

With a slicer week 5 is selected and week 0 - 1 are summarized in one bar

Maybe you can clarify your apporach a little bit.

Thanks in advance.

Michael

[amitchandak]

@Micha3lS , I usually create a week Rank and then use that from date of week table

New column

Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format

measures
This Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))

All previous week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]<=max('Date'[Week Rank])-1))