Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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
)
)
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
10 | |
9 | |
8 | |
7 | |
6 |
User | Count |
---|---|
15 | |
13 | |
11 | |
9 | |
8 |