DATEINPERIOD with Two column to pass in DAX for rolling SUM

[kuldeepjainesl]

The situation is to find a rolling sum of the last 12 months for a date column but also need to use another coloum to get the final range of valid dates to pass in a variable.

The first code works well for calculating rolling SUM.

LTFail-Communication = VAR NumOfMonths = 12
VAR LTFailCutoff = LOOKUPVALUE(
    FailureData_ICF_Trend[ProductMaster.CutOffWarrenty],
    FailureData_ICF_Trend[ProductMaster.prod221],"COMMUNICATION"
                              )
VAR LastCurrentDate =EOMONTH(
    MAX (FailureData_ICF_Trend[repairmonth]),0)
VAR Period =
    DATESINPERIOD ( 'Calender'[Date], LastCurrentDate, - NumOfMonths, MONTH )
VAR Result =
    CALCULATE (
      SUMX(
        VALUES ('Calender'[Date]), FailureData_ICF_Trend[FailQty_Trend]),Period,
                FailureData_ICF_Trend[ProductMaster.prod221]= "COMMUNICATION"
              )
VAR FirstDateInPeriod = MINX ( Period, 'Calender'[Date] )
VAR LastDateWithSales = EOMONTH(MAX ( FailureData_ICF_Trend[repairmonth] ),0)
RETURN
    IF ( FirstDateInPeriod <= LastDateWithSales, Result )

Now I need to modify this to use another dateinperiod to get the final valid data range. I am not able to figure out how to use the new VAR Period 1 in the Result variable along with the existing period.

LTFail-ToDeleteOriginal = VAR NumOfMonths = 12
VAR LTFailCutoff = LOOKUPVALUE(
    FailureData_ICF_Trend[ProductMaster.Life Warrenty.years],
    FailureData_ICF_Trend[ProductMaster.prod221],"COMMUNICATION"
                              )
VAR LastCurrentDate =EOMONTH(
    MAX (FailureData_ICF_Trend[repairmonth]),0)
VAR LastCurrentDateInvoice =EOMONTH(
    MAX (ShipmentData_ICF_T[invoicemonth]),0)
VAR Period =
    DATESINPERIOD ( 'Calender'[Date], LastCurrentDate, - NumOfMonths, MONTH )
VAR Period1 = DATESINPERIOD ( 'CalenderRepairCutoff'[Date], LastCurrentDateInvoice, - LTFailCutoff, YEAR )
VAR Result =
    CALCULATE (
      SUMX(
        VALUES ('Calender'[Date]), FailureData_ICF_Trend[FailQty_Trend]),Period,
                FailureData_ICF_Trend[ProductMaster.prod221]= "COMMUNICATION",
              )
VAR FirstDateInPeriod = MINX ( Period, 'Calender'[Date] )
VAR LastDateWithSales = EOMONTH(MAX ( FailureData_ICF_Trend[repairmonth] ),0)
RETURN
    IF ( FirstDateInPeriod <= LastDateWithSales, Result )

 

[kuldeepjainesl]

Basically it's like putting sumifs in Excel for Period Variable

 

[kuldeepjainesl]

Crossposted at https://community.powerbi.com/t5/De...s-in-DAX-for-rolling-SUM/m-p/3014495#M1027673