How to add overdue weeks to current week data

[RobertSparza]

Hello All,

I've been search a way to fix this but haven't found yet a solution.

This tables show the demand for an specific material; however our current Fiscal Week is number 14.
What i want to do is add from Week 10 to 14 demand, to be considered as current demand + Overdue demand, then continue with current demand as is from week 15 and on.

any clue on how to acheive this?

THanks!

[Anonymous]

Hi  @RobertSparza ,

Here are the steps you can follow：

1. Create calculated column.

week = WEEKNUM('Table'[Requirement Date],1)

Flag =
var _current=WEEKNUM(TODAY(),1)
return
IF(
    'Table'[week]<=_current,1,0)

2. Place [Flag]in Filters, set is=1, apply filter.

3. Result:

Today is week 15

Before filtering:

After filtering:

Please click here for the pbix file

Best Regards,

Liu Yang

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

[tamerj1 · Accepted solution]

Good morning @RobertSparza 
Here is a sample file with the solution https://www.dropbox.com/t/kukTzQD5LGRnmPBL

I have added a week number column in the same table. Not sure if you are using a date table but the approach would remain the same.

You can create your measure as follows

Qty = 
VAR WeekOfToday = 
    WEEKNUM ( TODAY() )
VAR CurrentWeekInFilter =
    SELECTEDVALUE ( Demand[Week Number] )
VAR Result =
    IF (
        CurrentWeekInFilter = WeekOfToday,
        CALCULATE ( 
            SUM ( Demand[Requirement Quantity] ),
            Demand[Week Number] <= WeekOfToday
        ),
        SUM ( Demand[Requirement Quantity] )
    )
RETURN
    Result

Hope this is the result that you're looking for. Have a great day!

[tamerj1]

Hi @RobertSparza 

do want to calculate the running total up to week 14? So week 11 will be the value of week 10 + week 11 and for week 12 will be 10+11+12 and son on? Or you just want only for week 14 to have to total of all previous weeks?  Please provide sample data and the code of your measure. 

[RobertSparza]

Hello Tamej,

I'm chasing to have total of previous weeks in week 14 and as example next week will be all the total of previous in week 15.

Here is the sample data, actually I'm doing like a supply demand for inventory management.

Material	Requirement Date	Requirement Quantity
ABCD	3/7/2022	3,424
ABCD	3/7/2022	9,600
ABCD	3/7/2022	8,560
ABCD	3/7/2022	24,000
ABCD	3/8/2022	4,280
ABCD	3/8/2022	12,000
ABCD	3/10/2022	4,708
ABCD	3/10/2022	13,200
ABCD	3/14/2022	12,840
ABCD	3/14/2022	36,000
ABCD	3/14/2022	8,560
ABCD	3/14/2022	24,000
ABCD	3/16/2022	17,237
ABCD	3/16/2022	48,327
ABCD	3/16/2022	17,120
ABCD	3/16/2022	48,000
ABCD	3/17/2022	17,120
ABCD	3/17/2022	48,000
ABCD	3/23/2022	4,280
ABCD	3/23/2022	12,000
ABCD	3/29/2022	12,840
ABCD	3/29/2022	36,000
ABCD	3/30/2022	13,226
ABCD	3/30/2022	37,080
ABCD	3/31/2022	13,161
ABCD	3/31/2022	36,900
ABCD	4/1/2022	17,463
ABCD	4/1/2022	48,960
ABCD	4/5/2022	13,161
ABCD	4/5/2022	36,900
ABCD	4/6/2022	15,396
ABCD	4/6/2022	43,164
ABCD	4/7/2022	17,548
ABCD	4/7/2022	49,200
ABCD	4/7/2022	24,108
ABCD	4/7/2022	7,704
ABCD	4/7/2022	21,600
ABCD	4/8/2022	4,409
ABCD	4/8/2022	12,360
ABCD	4/8/2022	12,360
ABCD	4/8/2022	8,599

The measure I'm trying:

Overdue = 
VAR Currentweek = WEEKNUM(TOday(),1)-1
VAR PastDueWeekValue =
    CALCULATE(
    SUMx(resb,RESB[Demand Balance Value]),
    RESB[Fin Week Num] < Currentweek)

RETURN
CALCULATE(
    SUMx(resb,RESB[Demand Balance Value]),
    RESB[Fin Week Num] >= Currentweek
   ) + PastDueWeekValue