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’...
  • 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]
)

     

     

     

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.