Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I have a table Sales that is linked to two date tables Date1 and Date2. Let's assume that the data looks like this:
Now I need the measure Sales to display the same data as on the second table while using the date table Date1:
I tried to use USERELATIONSHIP() but it doesn't work. Maybe I was using it wrongly.
Do you have any tips or ideas?
Thank you in advance!
Solved! Go to Solution.
Hi @KaiRado
Please refer to attached sample file with the solution
Sales Date2 =
SUMX (
VALUES ( 'Date 1'[Month] ),
CALCULATE (
VAR CurrentMonth = MAX ( 'Date 1'[Month Number] )
VAR Dates = FILTER ( VALUES ( 'Date 2'[Date] ), MONTH ( 'Date 2'[Date] ) = CurrentMonth )
VAR Result =
CALCULATE (
[Sales Amount],
TREATAS ( Dates, Sales[Order Date] ),
REMOVEFILTERS ( 'Date 1' )
)
RETURN
Result
)
)
Hi @KaiRado
Try using TREATAS
Sales =
CALCULATE (
SUM ( Sales[Sales] ),
TREATAS ( VALUES ( Date1[Date] ), Date2[Date] )
)
Seems I wrote it the other way around. Please try
Sales =
CALCULATE (
SUM ( Sales[Sales] ),
TREATAS ( VALUES ( Date2[Date] ), Date1[Date] )
)
Sorry I just saw your reply. Please try with REMOVEFILTERS
Sales =
CALCULATE (
SUM ( Sales[Sales] ),
TREATAS ( VALUES ( Date2[Date] ), Date1[Date] ),
REMOVEFILTERS ( Date1 )
)
Hi @tamerj1 , unfortunately I'm getting the same result as in the first table with Date1.
I'm not sure if it is even possible to achieve the resut I want.
Thank you so much for your time!
Hi @KaiRado
Please refer to attached sample file with the solution
Sales Date2 =
SUMX (
VALUES ( 'Date 1'[Month] ),
CALCULATE (
VAR CurrentMonth = MAX ( 'Date 1'[Month Number] )
VAR Dates = FILTER ( VALUES ( 'Date 2'[Date] ), MONTH ( 'Date 2'[Date] ) = CurrentMonth )
VAR Result =
CALCULATE (
[Sales Amount],
TREATAS ( Dates, Sales[Order Date] ),
REMOVEFILTERS ( 'Date 1' )
)
RETURN
Result
)
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
19 | |
15 | |
10 |
User | Count |
---|---|
57 | |
50 | |
44 | |
21 | |
19 |