Arrears for the last week measure

[adnanarain]

Hello,

Need help to calculate Arrears from previous week, Problem is that i have only arrears for the 1st week and formula for Arrears is

Previous Week Arrears + Previous Week Demand - Previous week Planned Shipment. Arrears will be calculated from previous week's result.

I am attaching example pbix.

3rd week's arrears should be 200 instead -25. (225 + 50) - 75 = 200

I am using following measure

Arrears_New = IF(Min('Example Data Format'[Plan_Week]) = 1, [Arrears], (CALCULATE (
    [Demand],
        FILTER (
            ALL ('Example Data Format'),
            COUNTROWS (
                FILTER (
                    'Example Data Format',
                    EARLIER ('Example Data Format'[Date]) = DATEADD('Example Data Format'[Date], -7, DAY)
                
                 )
            )
      )) + CALCULATE (
    [Arrears],
        FILTER (
            ALL ('Example Data Format'),
            COUNTROWS (
                FILTER (
                    'Example Data Format',
                    EARLIER ('Example Data Format'[Date]) = DATEADD('Example Data Format'[Date], -7, DAY)
                
                 )
            )
      ))) - CALCULATE (
    [Planned Shipment],
        FILTER (
            ALL ('Example Data Format'),
            COUNTROWS (
                FILTER (
                    'Example Data Format',
                    EARLIER ('Example Data Format'[Date]) = DATEADD('Example Data Format'[Date], -7, DAY)
                
                 )
            )
      )))

this is the result i need.

Thank you

https://drive.google.com/file/d/1w4XVucKAfrBJg8JvoEecHT_Cudyrab4Y/view?usp=share_link 

[Anonymous · Accepted solution]

Hi @adnanarain ,

Please have a try.

You can change the value 3 to the Forecasted week.

appeas =
VAR _1 = 250
VAR _minindex =
    MINX ( ALL ( 'Table' ), 'Table'[week] )
VAR _demand =
    SUMX (
        FILTER ( ALL ( 'Table' ), 'Table'[week] <= SELECTEDVALUE ( 'Table'[week] ) - 1 ),
        'Table'[demand]
    )
VAR _planned =
    SUMX (
        FILTER ( ALL ( 'Table' ), 'Table'[week] <= SELECTEDVALUE ( 'Table'[week] ) - 1 ),
        'Table'[planned]
    )
VAR _1re =
    IF ( MAX ( 'Table'[week] ) = _minindex, _1, _1 + _demand - _planned )
RETURN
    _1re

Measure2 =
VAR _demand1 =
    SUMX (
        FILTER (
            ALL ( 'Table' ),
            'Table'[week] <= SELECTEDVALUE ( 'Table'[week] )
                && 'Table'[week] >= 4
        ),
        'Table'[demand]
    )
VAR _planned1 =
    SUMX (
        FILTER (
            ALL ( 'Table' ),
            'Table'[week] <= SELECTEDVALUE ( 'Table'[week] )
                && 'Table'[week] >= 4
        ),
        'Table'[planned]
    )
VAR _re =
    MAXX ( FILTER ( ALL ( 'Table' ), 'Table'[week] = 3 ), [appeas] )
VAR _1re = _re + _demand1 - _planned1
RETURN
    IF ( MAX ( 'Table'[week] ) > 3, _1re, [appeas] )

Measure 3 = IF(MAX('Table'[week])>=3, MAX('Table'[demand])+[Measure 2],BLANK())

Best Regards
Community Support Team _ Rongtie

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

[Anonymous]

Hi @adnanarain ,

I have created a simple sample, please refer to it to see if it helps you.

Create a measure.

Measure =
VAR _1 = 250
VAR _demand =
    SUMX (
        FILTER (
            ALL ( 'Example Data Format' ),
            'Example Data Format'[Plan_Week]
                < SELECTEDVALUE ( 'Example Data Format'[Plan_Week] )
                && 'Example Data Format'[Type] = "Demand"
        ),
        [Demand]
    )
VAR _planned =
    SUMX (
        FILTER (
            ALL ( 'Example Data Format' ),
            'Example Data Format'[Plan_Week]
                < SELECTEDVALUE ( 'Example Data Format'[Plan_Week] )
                && 'Example Data Format'[Type] = "Planned Shipments"
        ),
        [Planned Shipment]
    )
RETURN
    IF ( MAX ( 'Example Data Format'[Plan_Week] ) = 1, _1, _demand - _planned + _1 )

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

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

[adnanarain]

@Anonymous 

Thank you so much. It worked like a charm.Can you please see below excel and help me to get last 2 calculations (i.e Arrears (Forecasted), Arrears Recovery).

https://docs.google.com/spreadsheets/d/1oWBKICD6Sbjth4TnvTyXIfolCs3nn9Py/edit?usp=sharing&ouid=106492281723695134831&rtpof=true&sd=true 

Data is same as we already had in previous pbix.

Thank you so much again

[Anonymous]

@Ritaf1983 

[Anonymous]

@v-cgao-msft 

[adnanarain]

@v-cgao-msft Please help

@v-chrs 

[Anonymous]

@amitchandak