Sale Summed by Date in Different Tables

[Sepefrio]

Tables

Contract Calendar - Columns = Month, Start Date, End Date

Fiscal Calendar - Columns = Period, Start Date, End Date

Regular Calendar - Columns = Month, Start Date, End Date

Sales History - Columns = Item, qty, Unit price, Sale Date,   Measure = Total Sales 

I have the 4 tables above. I need to group sales by Month/Period for each of the calendars. Each calendar is slightly different so the same dates can't be used for each one, but I'm pretty sure the same method can be used. 

My question, is there a way to sum 'Sales History'[Total Sales] base on the date ranges in the other tables without having to write an IF statment for each one or having to write the actual dates each time? Can I just refer to the starting and end dates in the Calendar tables? Then basically I would be able to copy the code each time, only changing the Table name as needed?

Something like:

Sum 'Sales History'[Total Sales]

       If the 'Sales History'[Sale Date] is between 'Contract Calendar'[Start Date] and 'Contract Calendar'[Start Date]

Thanks! 

[v-yueyunzh-msft · Accepted solution]

Hi , @Sepefrio 

Based on your description, it seems that you want to calculate the total value of the total sales in the fact table Sales History table in the measure based on the dates in another calendar table, right?

If that's the case, I think you could try combining the calculate() function with the filter() function to implement filter evaluation:

Measure =
CALCULATE (
SUM ( 'Sales History'[Total Sales] ),
FILTER (
ALL ( SalesHistory ),
'Sales History'[Sale Date] >= SELECTEDVALUE ( 'Contract Calendar'[Start Date] )
&& 'Sales History'[Sale Date] <= SELECTEDVALUE ( 'Contract Calendar'[End Date] )
)
)
If the above DAX expression does not meet your needs or I misunderstand your needs, you can provide your simple sample data and your expected output for us to further study and test and give you the desired output you want.

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Sepefrio]

Yes you understood it exactly and this worked perfectly, thank you!

[amitchandak]

@Sepefrio , one of the ways to deal with this is we populate a column in sales table with contract no

Maxx(filter(Contract , sales[Date] >= Contract[Start Date] &&, sales[Date] <= Contract[End Date] ) , [COntrcat No])

You can add additional conditions for customer or product as per need

refer 4 ways to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8