Forum Discussion
Calculate Actual vs Target that has specific dates
Hello, I am getting started with power bi and I have an issue with calculating the variance between actual values and target values. I have two table actual and target table. the actual table i...
Ashish_Mathur
Super User
Super UserHi,
These calculated column formulas work in the Actual table.
Next nearest target date=CALCULATE(MIN(Target[target date]),FILTER(Target,Target[id]=EARLIER(Actual[Id])&&Target[target date]>=EARLIER(Actual[Actual date])))Past nearest target date=CALCULATE(max(Target[target date]),FILTER(Target,Target[id]=EARLIER(Actual[Id])&&Target[target date]<=EARLIER(Actual[Actual date])))Target date to be considered=if(ISBLANK(Actual[Past nearest target date]),Actual[Next nearest target date],if(ISBLANK(Actual[Next nearest target date]),Actual[Past nearest target date],if(abs(Actual[Actual date]-Actual[Next nearest target date])<abs(Actual[Actual date]-Actual[Past nearest target date]),[Next nearest target date],[Past nearest target date])))Target value=LOOKUPVALUE(Target[target value],Target[id],Actual[Id],Target[target date],Actual[Target date to be considered])Hope this helps.
Hello sir,
Thank you for your reply, I applied the calculation. but I want it if the actual date is passed the target date get the next target date.
For example
- I have three targets dates: 15-March, 30-June, 31-Dec.
- I have three actuals dates: 30-April, 31-Aug, 31-Dec.
- So for the 30-April, I want to get the date 30-june.
- So for the 30-Aug I want to get 31-Dec
- And for the 31-Dec I want to 31-Dec.
Is there any possible way to implement this? Here is my pbix where i did implement the calculation: https://drive.google.com/file/d/1mqsOnlm0Er1jqz6pYIrD9dApckloZvbi/view?usp=sharing
Thank you for your reply.
- Ashish_Mathur
You have just changed the question. On the PBI file that you have shared, show the expected result very clearly.
