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 Team,
I needed your kind assistance with the below . ..
The .pbix file can be found here
Solved! Go to Solution.
Hey, @kagy100
According to your description, I created data to reproduce your scenario. The pbix file is attached at the end.
Table A:
Table B:
There's a relationship between tables. You can create measures as follows.
Start Month =
FORMAT(
CALCULATE(
MAX('Table A'[Date]),
FILTER(
ALL('Table A'),
[Date]<=MAX('Table B'[Motor Enhancement Date])
)
),
"mmm-yy"
)
End Month =
FORMAT(
CALCULATE(
MIN('Table A'[Date]),
FILTER(
ALL('Table A'),
[Date]>MAX('Table B'[Motor Enhancement Date])
)
),
"mmm-yy"
)
Start Month Value =
var startdate =
CALCULATE(
MAX('Table A'[Date]),
FILTER(
ALL('Table A'),
[Date]<=MAX('Table B'[Motor Enhancement Date])
)
)
return
CALCULATE(
SUM('Table A'[Motor Value]),
FILTER(
ALL('Table A'),
[Date]<=startdate
)
)
End Month Value =
var enddate =
CALCULATE(
MIN('Table A'[Date]),
FILTER(
ALL('Table A'),
[Date]>MAX('Table B'[Motor Enhancement Date])
)
)
return
CALCULATE(
SUM('Table A'[Motor Value]),
FILTER(
ALL('Table A'),
[Date]>=enddate
)
)
Result:
Best regards
Allan
If this post helps, then consider Accepting it as the solution to help other members find it faster.
Hey, @kagy100
According to your description, I created data to reproduce your scenario. The pbix file is attached at the end.
Table A:
Table B:
There's a relationship between tables. You can create measures as follows.
Start Month =
FORMAT(
CALCULATE(
MAX('Table A'[Date]),
FILTER(
ALL('Table A'),
[Date]<=MAX('Table B'[Motor Enhancement Date])
)
),
"mmm-yy"
)
End Month =
FORMAT(
CALCULATE(
MIN('Table A'[Date]),
FILTER(
ALL('Table A'),
[Date]>MAX('Table B'[Motor Enhancement Date])
)
),
"mmm-yy"
)
Start Month Value =
var startdate =
CALCULATE(
MAX('Table A'[Date]),
FILTER(
ALL('Table A'),
[Date]<=MAX('Table B'[Motor Enhancement Date])
)
)
return
CALCULATE(
SUM('Table A'[Motor Value]),
FILTER(
ALL('Table A'),
[Date]<=startdate
)
)
End Month Value =
var enddate =
CALCULATE(
MIN('Table A'[Date]),
FILTER(
ALL('Table A'),
[Date]>MAX('Table B'[Motor Enhancement Date])
)
)
return
CALCULATE(
SUM('Table A'[Motor Value]),
FILTER(
ALL('Table A'),
[Date]>=enddate
)
)
Result:
Best regards
Allan
If this post helps, then consider Accepting it as the solution to help other members find it faster.
Hey @kagy100 ,
please consider creating a pbix file that contains the sample data from above. Upload the pbix to onedrive or dropbox and share the link.
I'm not sure what you have, meaning what data is available and what data needs to be calculated. I'm also wondering if a relationship between both tables can be established.
Regards,
Tom
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 |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |