Reduce the Number of DAX

kuldeepjain — Mon, 30 Jan 2023 17:09:33 GMT

First DAX

LTFail-WC = VAR NumOfMonths = 12 VAR ProductFamily = "WHOLE CURRENT" VAR LTFailCutoff = LOOKUPVALUE( FailureData_ICF_Trend[ProductMaster.CutOffWarrenty], FailureData_ICF_Trend[ProductMaster.prod221],ProductFamily ) 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]= ProductFamily, FailureData_ICF_Trend[invoicemonth] >= LTFailCutoff ) VAR FirstDateInPeriod = MINX ( Period, 'Calender'[Date] ) VAR LastDateWithSales = EOMONTH(MAX ( FailureData_ICF_Trend[repairmonth] ),0) RETURN IF ( FirstDateInPeriod <= LastDateWithSales, Result )

Second DAX

LTShipment-WC = VAR ProductFamily = "WHOLE CURRENT" VAR NumOfMonths = LOOKUPVALUE(ShipmentData_ICF_T[ProductMaster.Life Warrenty.years],ShipmentData_ICF_T[ProductMaster.prod221],ProductFamily)*12 VAR LastCurrentDate = EOMONTH( MAX (ShipmentData_ICF_T[invoicemonth]),0) VAR Period = DATESINPERIOD ( 'Calender'[Date], LastCurrentDate, - NumOfMonths, MONTH ) VAR Result = CALCULATE ( SUMX( VALUES ( 'Calender'[Date] ), ShipmentData_ICF_T[ShipQTY_T] ), Period, ShipmentData_ICF_T[ProductMaster.prod221] =ProductFamily ) VAR FirstDateInPeriod = MINX ( Period, 'Calender'[Date] ) VAR LastDateWithSales = EOMONTH(MAX ( ShipmentData_ICF_T[invoicemonth] ),0) RETURN IF ( FirstDateInPeriod <= LastDateWithSales, Result )

Third DAX is :

Overall_LT_ICF% = DIVIDE([LTFail-WC],[LTShipment-WC])

How to Bring all these in one. I will plot this on a line chart for plotting trends over time series.

Re: Reduce the Number of DAX

AlB — Mon, 30 Jan 2023 17:17:36 GMT

Hi kuldeepjain

What do you mean exactly by "bring all these in one"?

Please accept the solution when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Re: Reduce the Number of DAX

kuldeepjain — Mon, 30 Jan 2023 17:22:47 GMT

Bring in one means, the first two dad are giving me rolling sum and the third will calculate the %

If I can do all these three step in one day, my queries will be short and I need not to write 3 dad for each product type.

topic Re: Reduce the Number of DAX in DAX Commands and Tips

Reduce the Number of DAX

Re: Reduce the Number of DAX

Re: Reduce the Number of DAX