How to Calculate Sum of 'Overall Pending' for 30-Day Lock

[fabric_ba]

Hi,

I have two tables, 'Input' and 'Date', with the following relationships:

- An **active** relationship between `Date[Date]` and `Input[Start date]`.

- An **inactive** relationship between `Date[Date]` and `Input[Updated on]`.

### Context:

- **'Start date'**: The date when a demand is for.(Jan24 to Jan 25) here Jan 24 to July 24 is actuals. Aug 24 to Jan 25 is forecasted

- **'Updated on'**: The date when the data is released. (Jan24-Aug24) everymonth one file is released

I'm trying to implement a "30-day lock" period, which means for each selected month, I want to sum the 'Overall Pending' values based on the 'Updated on' dates from the previous month.
For example: Refer the attachment

- If the matrix column is July-24, the measure should sum the 'Overall Pending' where `by referring the data released on `Input[Updated on]`June and give the result for `Input[Start date]` July.

- Similarly, for June-24, it should consider May-24 data, and so on.

### Matrix Visual Setup:

- **Rows**: 'Catergory data' from the 'Input' table.

- **Columns**: 'Month_Year' from the 'Date' table.

- **Values**: Requested dax The calculated sum of 'Overall Pending' for the previous month's data.

I've attempted to use `USERELATIONSHIP` and `PARALLELPERIOD`, but it didn’t work.

Thank you!

[v-xingshen-msft]

Hi @fabric_ba ,

Has your problem been solved after all this time, or has a new problem arisen, if there are any other questions on this issue, feel free to contact me and I'll get back to you as soon as I receive the message.

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

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

[fabric_ba]

Thank you very much for the response @bhanu_gautam 

But the results are not as expected, so I'd like to provide more context. Currently, we are in August 2024. In the column for August 2024, I want to check the data released, July 2024 (from Input[Updated on]), to see the predictions made for August 2024(`Input[Start date]` ) only .

Similarly, in the column for July 2024, I want to check the data from June 2024 (from Input[Updated on]) to see the predictions made for July 2024.

[fabric_ba]

Hi @bhanu_gautam 
Again it shows some error. Im attaching the Input dummy data and the expected output. Please refer to it.

[v-xingshen-msft]

Hi All,
Firstly bhanu_gautam thank you for your solution!
And @fabric_ba for you problem,In my understanding, you want to show the overall panding of the previous month in the next month, then we can use calculated columns to achieve this requirement.

PreviousDayPending = 
VAR CurrentDate = 'Input_Table_Monthly'[Updated on]
VAR previousMonth1 =DATEADD('Input_Table_Monthly'[Updated on],-1,MONTH)
VAR A=CALCULATE(
           MAX('Input_Table_Monthly'[Overall Pending]),
           FILTER(
               'Input_Table_Monthly',
               'Input_Table_Monthly'[Updated on] =previousMonth1  &&
               'Input_Table_Monthly'[Category] = EARLIER('Input_Table_Monthly'[Category])
           )
)
RETURN
IF( ISBLANK(A),
0,
A
)

If you have questions, check out my attachment.

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

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

[bhanu_gautam]

Thanks for additional context try updating measure as

OverallPendingPreviousMonth =

CALCULATE(

    SUM('Input'[Overall Pending]),

    USERELATIONSHIP('Date'[Date], 'Input'[Updated on]),

    'Input'[Updated on] = [PreviousMonthDate],

    USERELATIONSHIP('Date'[Date], 'Input'[Start date])

)

[bhanu_gautam]

@fabric_ba , Try using below measure

OverallPendingPreviousMonth =
VAR SelectedMonth = MAX('Date'[Date])
VAR PreviousMonth = EOMONTH(SelectedMonth, -1) -- End of the previous month
RETURN
CALCULATE(
SUM('Input'[Overall Pending]),
USERELATIONSHIP('Date'[Date], 'Input'[Updated on]), -- Activate the inactive relationship
'Input'[Start date] >= EOMONTH(PreviousMonth, 0) + 1, -- Start date is in the current month
'Input'[Start date] <= EOMONTH(SelectedMonth, 0) -- End date is in the current month
)