topic Re: DAX responds to 2 different timestamps in DAX Commands and Tips

DAX responds to 2 different timestamps

Bobster — Mon, 16 Jan 2023 11:27:19 GMT

Hi, I want to find a way to create 1 measure that assosiates to 2 different timestamps.

My data has 3 columns:

1) Sales Date (Year)

2) Departure Date (Year)

3) Sales

i.e.

Sales Date | Depature Date | Sales

2018 | 2018 | $1000

2018 | 2019 | $500

2019 | 2019 | $2000

2019 | 2020 | $3000

I wanted to create a static 2019 sales measure - depends on my column selected in my visual:

1) Sales Date: Sales (2019) = $5000

2) Departure Date: Sales (2019) = $2500

So somehow I need to embed this logic in the Calulate(SUM(sales)) filter:

Sales Date = 2019, Departure Date = Any

Sales Date = Any, Depature Date = 2019.

Appreciate if anyone can share some idea.

Thanks.

Re: DAX responds to 2 different timestamps

tamerj1 — Mon, 16 Jan 2023 12:28:24 GMT

Hi Bobster
Please refer to attached sample file wit the solution

Sales Amount = SWITCH ( TRUE ( ), ISFILTERED ( 'Table'[Sales Date ] ), CALCULATE ( SUM ( 'Table'[ Sales] ), 'Table'[Sales Date ] = 2019, ALL ( 'Table' ) ), ISFILTERED ( 'Table'[ Depature Date ] ), CALCULATE ( SUM ( 'Table'[ Sales] ), 'Table'[ Depature Date ] = 2019 ), SUM ( 'Table'[ Sales] ) )

Re: DAX responds to 2 different timestamps

Bobster — Tue, 17 Jan 2023 00:10:01 GMT

Hi tamerj1 , appreciate your help. I've slightly modified the DAX, i.e. removed the "ALL (Table)" so the measure gives me the breakdown when other columns were added i.e. Month, and it worked.

Only out of curiosity, the ISFILTERED detecte 1 table/field at a time. What if user want to display and filter sales Sold and Departed in 2019 ($2000)?

Thanks