topic Re: 30 days lock calculation in DAX Commands and Tips

30 days lock calculation

fabric_ba — Wed, 07 Aug 2024 06:11:54 GMT

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 made.

**'Updated on'**: The date when the data is released.

### Matrix Visual Setup: -

**Rows**: 'Category data' from the 'Input' table.

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

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:

- 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.

I have attached an image with the input file and the expected result. I’m attaching the pbi file.30 day lock file <- pbi file updated

Re: 30 days lock calculation

SachinNandanwar — Wed, 07 Aug 2024 05:54:25 GMT

Hi,

The pbi file is no longer available

Re: 30 days lock calculation

fabric_ba — Wed, 07 Aug 2024 06:12:53 GMT

Hi SachinNandanwar
I have updated the link.

Re: 30 days lock calculation

SachinNandanwar — Wed, 07 Aug 2024 07:32:57 GMT

Hi fabric_ba ,

There are 3 updates for C3 in the month of Feb

But your expected output shows the count as 1.

Re: 30 days lock calculation

fabric_ba — Wed, 07 Aug 2024 07:37:50 GMT

Yes SachinNandanwar , The numbers are based on start date. For the Feb 24 we need to look at Updated on Jan 24 in that we have only one start date for Feb 24.

Re: 30 days lock calculation

Anonymous — Wed, 07 Aug 2024 10:05:23 GMT

Hi fabric_ba ,

Do you want to classify the C1, C2, and C3 of the corresponding previous month of the previous year according to the month of 2024, and put the result Total into a matrix?

If so,I did a test for your reference.

In my scenario:

My Model View:

I created a new table and sorted it in ascending order based on the month in the table.

Table = ADDCOLUMNS( CALENDAR(DATE(2024,1,1), DATE(2024,12,31)) , "Month Year" , FORMAT([Date],"MMM yyyy") ,"Month" , MONTH([Date]) )

The months in the table are sorted in ascending order:

My Report View:

I created two measures, one to determine whether the month +1 in the 'Input' table is greater than the month in the 'Table' table, and if so, the statistics are displayed in the matrix, and one to show the C1, C2, and C3 values of statistics:

Measure = MONTH( CALCULATE( MAX('Input'[Updated_on]), ALL('Input')) ) +1>= MAX('Table'[Month])

Measure 2 = IF(MONTH( CALCULATE( MAX('Input'[Updated_on]), ALL('Input')) ) +1>= MAX('Table'[Month]), CALCULATE( SUM('Input'[Overall Pending]) ,YEAR('Input'[Updated_on]) = YEAR( MAX('Table'[Date]) ) -1 && MONTH('Input'[Updated_on])+1 = MONTH( MAX('Table'[Date]) ) )+0 )

Best Regards,

Sunshine Gu

Re: 30 days lock calculation

fabric_ba — Wed, 07 Aug 2024 09:44:02 GMT

Thank you, Anonymous for your solution.

Let me explain the use case. This data pertains to Human Resources. Every month, a file is released containing actuals and forecasts of employee demand and its appended. The "Updated on" column indicates the release date of the file, while the "Start date" represents the date of the demand.

For the 30-day lock, I'm trying to compare the predicted demand for a given month with the corresponding predictions in the previously released file.

Re: 30 days lock calculation

SachinNandanwar — Wed, 07 Aug 2024 09:58:57 GMT

There are 2 duplicate entries for C1 for the update month of April.

Should they be counted as two seperate entries ? If yes then there has to be an unique identifier .

Re: 30 days lock calculation

fabric_ba — Wed, 07 Aug 2024 10:08:44 GMT

Hi SachinNandanwar
We can have mulitple entries, Updated on is the date when file/data is released.

Re: 30 days lock calculation

SachinNandanwar — Wed, 07 Aug 2024 10:10:36 GMT

There are 2 entries with the same UpdateDate and StartDate.

Re: 30 days lock calculation

fabric_ba — Wed, 07 Aug 2024 10:22:12 GMT

We can have a duplicate

Re: 30 days lock calculation

SachinNandanwar — Wed, 07 Aug 2024 10:48:21 GMT

If you want the duplicate records to be counted as one value then use a this measure formula

Measure_CountRows = Var _Cal=CALCULATE(Countrows(FILTER ( ADDCOLUMNS ( SUMMARIZE ( ( Input ), Input[Category], 'Date'[Month], 'Date'[Year], Input[Updated_on] ), "StartDateMonthNo", 'Date'[Month], "Cnt", CALCULATE ( COUNT ( Input[Category] ), USERELATIONSHIP ( 'Date'[Date], Input[Start Date] ) ) ), FORMAT ( Input[Updated_on], "MM" ) + 1 = 'Date'[Month] ))) RETURN _Cal

Else use this to create a summarized table

Table = FILTER ( ADDCOLUMNS ( SUMMARIZE ( ( Input ), Input[Category], 'Date'[Month], 'Date'[Year], Input[Updated_on] ), "StartDateMonthNo", 'Date'[Month], "Cnt", CALCULATE ( COUNT ( Input[Category] ), USERELATIONSHIP ( 'Date'[Date], Input[Start Date] ) ) ), FORMAT ( Input[Updated_on], "MM" ) + 1 = 'Date'[Month] )