Forum Discussion

fabric_ba's avatar
fabric_ba
Frequent Visitor
2 years ago
Solved

30 days lock calculation

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

  • SachinNandanwar's avatar
    SachinNandanwar
    2 years ago

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

     

     

     

11 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      fabric_ba
      Frequent Visitor
      2 years ago

      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.