topic Re: Relative Date using multiple dates in DAX Commands and Tips

Relative Date using multiple dates

Tob_P — Fri, 08 Jul 2022 14:19:17 GMT

Hi there - just trying to see if this is possible or if anyone has done something similar.

I have a table that shows product sales and you can slice on those salespeople within in. However I want to only show sales after a certain date for a number of people, and that date is different in 3 ways...

1. A group of people get sales from day 0 - they've worked for the company since that date and get credited for all sales

2. A group of people get sales from the day they started working for the company

3. A group of people get sales from one specified date in the future.

Is it possible for that table to work to all of these rules when sliced by Salesperson?

Thanks

Re: Relative Date using multiple dates

johnt75 — Fri, 08 Jul 2022 14:28:31 GMT

If you have a column on your sales person table which indicates the date from when they should receive credit for the sales, then you could create a measure like

Filtered sales = SUMX ( 'Sales person', CALCULATE ( [Sales amount], KEEPFILTERS ( 'Date'[Date] >= 'Sales person'[Start date] ) ) )

Re: Relative Date using multiple dates

Tob_P — Tue, 26 Jul 2022 18:55:30 GMT

@johnt75

Sorry for delayed response. Have given this a go but I'm not quite there...

So my Saleserson Start Date in in TBlTargets (2) as is the saleperson name, code etc, the Sales Amount is in Nav_Sales, I have my Date table fine but when I look to bring in the Start Date from TblTargets (2), it will only allow me to select either the Date table or a Measure from another table.

For info, I had to substitute Calculate [sales amount] from your suggestion above, as the Sales Amount isn't a measure.

Any guidance appreciated!

Re: Relative Date using multiple dates

Tob_P — Tue, 26 Jul 2022 19:01:53 GMT

Realised that the Start Date wasn't marked as Date but that still doesn't make a difference.

Re: Relative Date using multiple dates

johnt75 — Wed, 27 Jul 2022 08:44:28 GMT

I think that might just be Intellisense being rubbish. As you're iterating over 'TblTargets(2)' you have a row context so you can access any column from that table. Try just typing in the fully qualified name, i.e. 'TblTargets(2)'[Start date]

Re: Relative Date using multiple dates

Tob_P — Wed, 27 Jul 2022 11:03:12 GMT

Gave that another go typing it out from scratch but no use again. Have tried it an alternative way...

So just for my sanity to make sure that I'm conveying myself properly. The Sales date that I need to filter on lives in TblTargets(2) (there are 2 dates, start date and product bonus start date, with the latter being the date that I want them to get bonus sales from).

Nav_Sales History MASTER holds the £amount and also holds the Order Date. So if the Product Bonus Start Date is 01/11/22, then any sales with an order date >= 01/11/22 should be output. So when I amend your suggestion to make sure the order date is include (as I think it's necessary), then when I try to include the Product Bonus Start Date column which sits in 'TblTargets (2), I cannot do that. The only options are either measures or the Nav_Sales History MASTER Table. I have tried to use RELATED like below...but no luck either

Re: Relative Date using multiple dates

johnt75 — Wed, 27 Jul 2022 11:09:30 GMT

Try storing the bonus start date in a variable

Product target filtered sales = SUMX ( 'TblTargets(2)', VAR bonusStartDate = 'TblTargets(2)'[Product bonus start date] RETURN CALCULATE ( SUM ( 'Nav_Sales History MASTER'[Amount (LCY)] ), KEEPFILTERS ( 'Nav_Sales History MASTER'[Order date] >= bonusStartDate ) ) )

Re: Relative Date using multiple dates

Tob_P — Wed, 27 Jul 2022 12:48:02 GMT

@johnt75

Great stuff! Works a treat. Thanks for helping out!