New wins by comparing in 2 different time periods

DAX DAX Calculations
1

Im trying to calculate new customer wins. I went through the customer related solutions but they are not similar to my logic. Below is the image with the flow for the logic.

image
image.png928×685 21.4 KB

and below is the example.

image
image.png1043×337 17.2 KB

I am able to achieve this, however it takes a long time to load result. to me the DAX I have written are simple and it shouldn’t take a long time, however i’m not sure if there is an efficient way to achieve this, really really looking for help. If its one month data it doesnt take a long time, when it comes to one year data it takes time.

Below are the fields I created in Sales Table.

This is the data model:

Data%20model
Data model.PNG830×785 85.4 KB

and example screenshot

example
example.PNG1043×337 17.3 KB

Below are the steps I did and achieved the calculation, but it takes long time in returning result, record count for SALES table is only 4 million. Although memory issue is there, but i really need help to know if there is a better way to do this.

The reason I created the measures separately is that we should be able to see the time frames dates if needed.

  1. I created combined calculated column for customer number and drawing formula.

  2. created measure for time frame 1:

    TF1 Date:=
    VAR startdate =DATE(YEAR(STARTOFMONTH(‘Calendar’[Date]))-1,MONTH(STARTOFMONTH(‘Calendar’[Date])),DAY(STARTOFMONTH(‘Calendar’[Date])))
    VAR enddate = ENDOFMONTH(‘Calendar’[Date])

    VAR TF1Date2=CALCULATE(MIN(Sales[REVENUE_DATE]),ALLEXCEPT(Sales,Sales[Customer],Sales[Drawing Formula]),FILTER(Sales,Sales[REVENUE_DATE]))

    RETURN TF1Date2

  3. created measure for time frame 2 Start Date :

    TF2 StartDate:=
    VAR startdate =LOOKUPVALUE(‘Calendar’[LastDayOfMonth],‘Calendar’[Date],Sales[TF1 Date])
    RETURN startdate

  4. created measure for time frame 2 End Date:

    TF2 EndDate:=
    VAR initialdate=Sales[TF2 StartDate]+1
    VAR seconddate =LOOKUPVALUE(‘Calendar’[Last Year Date],‘Calendar’[Date],initialdate)
    VAR enddate = LOOKUPVALUE(‘Calendar’[Last Year Date],‘Calendar’[Date],seconddate)
    RETURN enddate

  5. Using timeframe 2 start date and end date from points 4 and 5, i created next measure timeframe 2 date:

    TF2 Date:=
    VAR startdate =Sales[TF2 EndDate]
    VAR enddate = Sales[TF2 StartDate]
    VAR TF2Date2 = CALCULATE(MIN(Sales[REVENUE_DATE]),DATESBETWEEN(‘Calendar’[DATE],startdate,enddate),ALLEXCEPT(Sales,Sales[Customer],Sales[Drawing Formula]))
    RETURN TF2Date2

  6. created measure to return end of month of Time frame 2 date:

    TF2 EO EndDate:=
    VAR initialdate=Sales[TF2 Date]
    VAR seconddate =LOOKUPVALUE(‘Calendar’[LastDayOfMonth],‘Calendar’[Date],initialdate)
    RETURN seconddate

  7. created measure to return the month difference

    TF MonthCount:=
    Var selectedendperiod = ENDOFMONTH(‘Calendar’[Date])
    Var enddate = Sales[TF2 EO EndDate]
    VAR MC1 =SUMX(
    VALUES(Sales[CPDF]),
    IF(OR(OR(enddate=blank(),enddate=selectedendperiod) , enddate > selectedendperiod),1,
    DATEDIFF(enddate,selectedendperiod,MONTH)
    )
    )

    RETURN MC1

8.Created the new win check measure which returns 0 or 1

New Win:= 
VAR new =
CALCULATE(
                    MINX(
                        SUMMARIZE(Sales,Sales[Drawing Formula],Sales[Customer],'Item'[ITEM_DESC]),
                        IF(Sales[Drawing Formula]=BLANK(), 0, 
                           IF([TF MonthCount]<13,1,0))
                  )
)
RETURN new
  1. Finally return the sales for the new win customers

NewWin Sales:=SUMX(Sales,IF(sales[New Win]=0,0,Sales[SALES Revenue USD_FxAdj]))

Please help!

2

Ok thanks for all the information, I’ve read over this a few times now.

To me it certainly looks like there is too many iterations happening throughout all these formulas and that is why it’s taking a long time.

SUMX, MINX, LOOKUPVALUE - all if these need to iterate through entire tables to find results, so if you layer them one of another then you start getting into crazy numbers of iterations.

Past this, there is honestly a lot going on here and it’s really quite difficult to factor in everything that is at play here without seeing a demo of the data and data model.

Can I suggest setting up a simple Power BI model with only the information that is need for this one calculations. That way you can really drill down into this one issue and not have it take forever to load all the time due to your detailed model you currently have.

My thought on the formula as well is you could probably simplify it and not have as many steps or branches with your measures.

Potentially you could just have one iterating function like SUMX which does everything in one go. Honestly though I would need to see this in Power BI to understand more and see the logic happening firsthand.

Thanks
Sam

3

Thank you very much for your response Sam and looking into this question.

I observed that when it reaches the step 8: calculating New Win to return 0 or 1 is where it takes long time to load, also another challenge at this stage is that, if i display the result in matrix by MonthYear on columns, it messes up the calculation, but if I include month in summarize, it works but takes long time.

in short, the time consuming measure is summarization, is there any otherway to do it?

I would like to set this up on power bi, so that you could take a look at it, would adventure works data be sufficient for this exercise?

Please help.

4

Can’t assist any further without seeing a demo model really.

Welcome to set it up how you like, just make it as intuitive around the problem as possible.

I know myself that you shouldn’t never really need measure that are this complex. I never have them myself in any development I do, so it will all be about simplification.

Thanks
Sam