30 days lock calculation

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

[Anonymous]

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

[fabric_ba]

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.

[SachinNandanwar]

Hi,

The pbi file is no longer available

[fabric_ba]

Hi @SachinNandanwar 
I have updated the link.

[SachinNandanwar]

Hi @fabric_ba ,

There are 3 updates for C3 in the month of Feb

But your expected output shows the count as 1. 

[fabric_ba]

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.

[SachinNandanwar]

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 .

[fabric_ba]

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

[SachinNandanwar]

There are 2 entries with the same UpdateDate and StartDate.

[fabric_ba]

We can have a duplicate

[SachinNandanwar · Accepted solution]

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