Forum Discussion

sqlguru448's avatar
sqlguru448
Helper III
5 years ago

Complex Dax Measure - Help Needed

Hello Folks,

 

I have a complex requirement in Power BI reporting which is sourced from Azure Analysis Services Tabluar Model via Live Connection. I cannot change the Tabular model, so the measure or Dax needs to be implemented in Power BI report.

 

I have Sales Fact table and Customer, Product, Location Dimension table, the task is to pull Sales amount of only those customers who have ordered the specific Products from the same store/location within 90 days of Purchase date.

 

For example Customer A has Purchased Product 123 from location TX on 1/1/2020 with Amt $100

Customer B has Purchased Product 456 from Location CA on 2/1/2020  with Amt $10

Customer A has purchased Product 456 from Location TX on 2/28/2020 with Amt $100

Customer B has Purchased Product 456 from  Location CA on 8/1/2020 with Amt $10

Customer C has purchased Product 789 from Location NY on 5/1/2020 with Amt $1000

 

so my report should pull only Customer A as he has purchased different product at same location and store and within 90 days of initial/previous purchase date.

 

Please help, any help is really appreciated.

 

Thanks

 

 

14 Replies

    • CNENFRNL's avatar
      CNENFRNL
      Community Champion

      Hi, there, pls refer to the following calculated table formula for details,

       

      Debug = 
      VAR _summary =
          SUMMARIZECOLUMNS (
              dCust[Customer],
              dLoc[Location],
              dProd[Product],
              fSales,
              "Times", COUNTROWS(dDate),
              "MinDate", MIN ( dDate[Date] ),
              "MaxDate", MAX ( dDate[Date] ),
              "Interval", INT ( MAX ( dDate[Date] ) - MIN ( dDate[Date] ) )
          )
      VAR _tb =
          FILTER ( _summary, [Interval] > 0 && [Interval] <= 7 )
      RETURN
          _summary

       

       

      or change the last line to turn it into a measure

       

      RETURN
          --_summary
          CONCATENATEX(_tb, [Customer], UNICHAR(10))

       

      it's based on a most idealized data model

       

       

       

  • Hi there.

    sqlguru448, the task at hand is underspecified. Please make it more clear.

    You say "the task is to pull Sales amount of only those customers who have ordered the same Product from the same store/location within 90 days of Purchase date."

    It seems that such a calculation should only be performed when 1) only one product is visible and 2) one store/location has been selected. Please confirm this is true and if not, please give rules for the calculation in case the above conditions are violated.

    What happens when a customer bought product P on day D at store S, then the same product on day D + 20 at the same store and then the same product on day D + 200 at the same store? Is your condition an "OR" or "AND" condition? Should all the purchases be made within 90 days of some other or should there be at least one pair of purchases with this property?

    Thanks.

  • Thanks  @AntrikshSharma, I will look into the article.

     

    daxer-almighty , it is a OR, if a customer purchases same product within 90 days during the "time frame" i.e. start and end date of purchase date.

     

     

    CNENFRNL ,  how would this DAX look at same customer, location and product? it is just looking at the interval. Pleasee explain.

     

    Thank you.

    • CNENFRNL's avatar
      CNENFRNL
      Community Champion

      sqlguru448 

      Thanks to the powerful SUMMARIZECOLUMNS func, sales data can be summarized per dimensions like customer, location and product in one shot; similar to

       

      Select customer, location, product, MIN(date), MAX(date)
      From sales
      Group By customer, location, product

       

      As I mentioned, the calculated table is created for more clarity.

      • daxer-almighty's avatar
        daxer-almighty
        Solution Sage
        // This will return all the customers
        // visible in the current context who
        // in the selected period of time in the same
        // location bought at least(!) one and the same
        // product at least 2 times and the purchases were
        // made within at most 90 days within each
        // other. This measure is fully responding
        // to all filters, so please interpret it
        // carefully WITHIN THE CURRENT CONTEXT.
        // Transactions is the main fact table connected
        // to dimensions: Customer, Location,
        // Product, Date (joins to Transactions
        // on [Purchase Date]) via the standard
        // *:1 one-way relationship.
        
        [# Cust] =
        var __sections =
            // partition the transactions
            // by customer, product and
            // location
            SUMMARIZE(
                Transactions,
                Customer[CustID],
                Product[ProdID],
                Location[LocID]
            )
        // for each section, check if
        // there are at least 2 transactions
        // within at most 90 days of each
        // other and if there are, include
        // the section in the final set
        var __qualifiedSections =
            FILTER(
                __sections,
                CALCULATE(
                    // get all dates that are present
                    // in the transactions for the
                    // current section
                    var __sectionPurchaseDates =
                        SUMMARIZE(
                            Transactions,
                            Dates[Date]
                        )
                    var __datesIfNextDateWithin90Days =
                        FILTER(
                            __sectionPurchaseDates,
                            // calc the days to next purchase
                            var __currentDate = Dates[Date]
                            var __nextDate =
                                MAXX(
                                    FILTER(
                                        __sectionPurchaseDates,
                                        Dates[Date] < __currentDate
                                    ),
                                    Dates[Date]
                                )
                            var __daysBetweenDates =
                                __nextDate - __currentDate
                            return
                                __daysBetweenDates <= 90
                                &&
                                // this condition must be here
                                // since the next date is BLANK
                                // for the last date in question
                                __daysBetweenDates > 0
                        )
                    return
                        NOT ISEMPTY(
                            __datesIfNextDateWithin90Days
                        )
                        
                )
            )
        var __customersSurvivedCount =
            DISTINCTCOUNT(
                SELECTCOLUMNS(
                    __qualifiedSections,
                    "@CustID", Customer[CustID]
                )
            )
        return
            __customersSurvivedCount