Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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.
Solved! Go to Solution.
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] )
)
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
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] )
)
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
23 | |
21 | |
20 | |
13 | |
12 |
User | Count |
---|---|
43 | |
31 | |
24 | |
23 | |
22 |