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
Hello community,
I am beginner in power bi, and I would like your help to figure out my problem.
I have two tables linked by id:
I want to calculate the variance between the target and actual according to the target date and actual date.
here is my table:
for example for id 1: I want to know the variance between the sum of actual values that happened on/before the target date:
So before 30-march I have the three orange values, so the result will sum(50,3000,2000)- 2000
My result will be:
Is there any way to implement this on power bi?
Thank you for your help.
Solved! Go to Solution.
Hi @unkCandy ,
Please try:
First, create a measure:
variance =
VAR _a =
MAXX (
FILTER (
ALL ( 'target table' ),
[Id] = MAX ( 'target table'[Id] )
&& [target date] < MAX ( 'target table'[target date] )
),
[target date]
)
RETURN
CALCULATE (
SUM ( 'actual table'[actual] ),
FILTER (
'actual table',
[actual date] > _a
&& [actual date] <= MAX ( 'target table'[target date] )
)
)
- SUM ( 'target table'[target] )
Then apply it to the table visual:
Final output:
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @unkCandy ,
How to get the target value based on the actual date? Can you please explain it in more detail? Is it based on the nearest target date to the actual date? Or is it before or after the actual date?
Please provide me with more details about the logic.
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @unkCandy ,
Please try:
First, create a measure:
variance =
VAR _a =
MAXX (
FILTER (
ALL ( 'target table' ),
[Id] = MAX ( 'target table'[Id] )
&& [target date] < MAX ( 'target table'[target date] )
),
[target date]
)
RETURN
CALCULATE (
SUM ( 'actual table'[actual] ),
FILTER (
'actual table',
[actual date] > _a
&& [actual date] <= MAX ( 'target table'[target date] )
)
)
- SUM ( 'target table'[target] )
Then apply it to the table visual:
Final output:
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello,
I made a mistake explaining, I would like to calculate actual value -target value using target date.
I will explain, I want to extract the target value according to the actual date.
Here is an example:
My actual table is:
and my target table is as follows:
I want to calculate actual-target values as the target can be retreived from the target table according to actual date. For example:
My result will look like:
I hope you can help,
thank you again.
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 |
---|---|
94 | |
92 | |
83 | |
71 | |
49 |
User | Count |
---|---|
143 | |
120 | |
110 | |
59 | |
57 |