topic Re: How to Calculate Sum of 'Overall Pending' for 30-Day Lock in DAX Commands and Tips

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

fabric_ba — Tue, 06 Aug 2024 04:32:52 GMT

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!

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

bhanu_gautam — Tue, 06 Aug 2024 05:45:46 GMT

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
)

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

fabric_ba — Tue, 06 Aug 2024 06:44:24 GMT

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.

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

bhanu_gautam — Tue, 06 Aug 2024 11:24:48 GMT

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])

)

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

fabric_ba — Tue, 06 Aug 2024 11:41:21 GMT

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

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

Anonymous — Wed, 07 Aug 2024 09:05:54 GMT

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.

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

Anonymous — Tue, 10 Sep 2024 05:32:27 GMT

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.