kuldeepjainesl
Member
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.
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.
The first code works well for calculating rolling SUM.
Code:
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.
Code:
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 )