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

     

     

     

SachinNandanwar's avatar
SachinNandanwar
Icon for Impactful Individual rankImpactful Individual
2 years ago

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

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

  • SachinNandanwar's avatar
    SachinNandanwar
    Icon for Impactful Individual rankImpactful Individual
    2 years ago

    There are 2 entries with the same UpdateDate and StartDate.

    • fabric_ba's avatar
      fabric_ba
      Frequent Visitor
      2 years ago

      We can have a duplicate

      • SachinNandanwar's avatar
        SachinNandanwar
        Icon for Impactful Individual rankImpactful Individual
        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]
)