Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Measure to Sum Value based on Date Ranges in One Table Given Date From Another

I'm trying to formulate a DAX expression to SUM a total from table2's date ranges, based on a date within table1.Here's what Table1 looks like: TABLE1 CustomerID SellerID SaleDate SaleAmount ...
Need Help
  • Nathaniel_C's avatar
    Nathaniel_C
    6 years ago

    Anonymous ,

    It works as a measure.  Expected from you.

    From your pbix file.

     

     

    and without Seller ID.



     

    Just do not summarize SalesAmount.
    Let me know if you have any questions.

    If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
    Nathaniel

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi Nathaniel_C 

 

First off, thanks for responding! So I did what you said and modeled things as shown below.

 

But as you can see, not all is completely solved. You can see how the New Cost Measure is blank for one of the customers if I remove the Seller. 

This should be populated with $70 because the sale date ranges fall within the Customer Start & End dates and should be added up ($30 + $40). Any ideas?

amitchandak's avatar
amitchandak
Super User
6 years ago

If possible please share a sample pbix file after removing sensitive information.
Thanks

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Not sure what the best way to share files is, but I used gofile here:

     

    https://gofile.io/?c=OrDBr9

    • amitchandak's avatar
      amitchandak
      Super User
      6 years ago

      Can you create a new column (not measure) like this

       

      Cost Column = sumx( filter(Table2,Table2[CustomerEndDate]>=Table1[SaleDate] && Table1[SaleDate]>=Table2[CustomerStartDate] && Table1[CustomerID]=Table2[CustomerID]),Table2[CustomerCost])

      Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
      Thanks.

      My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago
        amitchandak wrote:

        Can you create a new column (not measure) like this

         

        Cost Column = sumx( filter(Table2,Table2[CustomerEndDate]>=Table1[SaleDate] && Table1[SaleDate]>=Table2[CustomerStartDate] && Table1[CustomerID]=Table2[CustomerID]),Table2[CustomerCost])

        Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
        Thanks.

        My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

         

        So, this technically works but still isn't a measure. Does DAX not allow this type of formulation as a measure..? The reason I ask is because this will make me have to put in a request to the IT group to add a column to their model instead of being able to incorporate this as a measure myself through Power BI.