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

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.