Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
kuldeepjain
Frequent Visitor

Reduce the Number of DAX

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.

2 REPLIES 2
AlB
Community Champion
Community Champion

Hi @kuldeepjain 

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

 

SU18_powerbi_badge

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.

 

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.

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.