Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

DAX Help - filtering the values in one table based on a date range in another table

I am having a problem showing sales values from one table where the sales date falls between a date range on another table.

 

Sales Table

Service Code, Posting Date, Sales Amount £, Quantity

 

Service Price History Table

Service Code, Price Change Start Date, Price Change End Date, Unit Price

 

There’s a many to many relationship between these two tables based on Service Code. Each Service Code could have had one or more price changes during the year.

 

What I want to end up with is a table for each Service Code that shows Unit Price, the start date and end date of when that Unit price was applicable, and then the Sales Amount £ and Quantity sold within each date range.

 

Currently the Sales Amount £ and Sales Quantity column is summed up for the whole year and is ignoring the Date Ranges.

 

 I thought perhaps I’d be able to do this by adding another column which uses DAX to return the Sales Amount £ and Sales Quantity only where the Posting Date is between the Start and End dates on the Service Price History Table.

 

Need Help

3 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

       

      Thank you for your help. I tried to add 2 calculated columns for Amount and Quantity, but it doesnt look like it's calculating the way I want it to.

       

      Here is the formula I used to add the columns:

       

      Amount =
      SUMX (
      FILTER  (Sales, Sales[Posting Date] >= 'ServicePrice History'[Start Date] && Sales[Posting Date] < 'ServicePrice History'[End Date]),
      Sales[Amount]
      )

       

      Quantity =
      SUMX (
      FILTER  (Sales, Sales[Posting Date] >= 'ServicePrice History'[Start Date] && Sales[Posting Date] < 'ServicePrice History'[End Date]),
      Sales[Quantity]
      )

       

      But as you can see from the picture, it's not returning figures I wouldve expected.

       

      I want a sum of the Amounts and Quantities from the Sales table where the Posting Date is between the Start and End Date on the Service Price History Table.

       

      So in this case the total Sales should be 1621.26 (because 540.428 x 3) and the Quantity should be 4.

       

      Any ides where I'm going wrong?

       

       

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Thanks. I tried the Calculated Column for Amount and one for Quantity but it didnt work the way I was expecting it to.

       

      Here is the DAX i used for the calculated colum for amount:

      Amount =
      SUMX (
      FILTER  (Sales, Sales[Posting Date] >= 'ServicePrice History'[Start Date] && Sales[Posting Date] < 'ServicePrice History'[End Date]),
      Sales[Amount]
      )
       
      And for Quantity:
       
      Quantity =
      SUMX (
      FILTER  (Sales, Sales[Posting Date] >= 'ServicePrice History'[Start Date] && Sales[Posting Date] < 'ServicePrice History'[End Date]),
      Sales[Quantity]
      )

       

      Have a look at the picture - I wouldve expected it to sum up the Sales Amounts of 540.42 + 540.42 + 540.42 + 0 = 1621.26. And I would have expected the quantity to be 4.

       

      Any ideas where I'm going wrong?