Calculate Actual vs Target that has specific dates

[mellania]

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 is as follow: 

Id	Actual date	Actual value
1	31-Jan	400
1	3-Mar	500
1	18-May	200
1	30-Sep	300
1	15-Dec	450
3	20-Feb	3000
3	14-Jun	4000
3	15-Aug	500
3	5-Dec	2000

and target table is as follow: 

id	target date	target value
1	31-Mar	200
1	30-Aug	100
3	31-Aug	3000
3	30-Dec	2000

I want to calculate actual- target values as the target can be retreived from the target table according to actual date. For example:

• For id=1, first row I have the date 31-Jan so I should the target value of the date 31-march
• for id=1, forth row, i have the date 30-Sep so i should get the target value of the date 30-Aug 

My result will look like as: 

Id	Actual date	Actual value	Target value	Variance
1	31-Jan	400	200	200
1	3-Mar	500	200	300
1	18-May	200	200	0
1	30-Sep	300	100	200
1	15-Dec	450	100	150
3	20-Feb	3000	3000	0
3	14-Jun	4000	3000	1000
3	15-Aug	500	3000	-2500
3	5-Dec	2000	2000	0

I want to implement it in dax but i dont know how to retrieve the target value according to actual date. 

I hope you can help me to do that, 

thanks.

[Anonymous]

Hi @mellania,

Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or Accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[Ashish_Mathur]

Hi,

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.

[mellania]

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.

[Anonymous]

Hi @mellania,

In fact, power bi data mode table does not contain row and column index so it not suitable or accurate extract the specific row/cell values. (You need to use filters to help Dax expression locate these fields values)
I'd like to suggest you add an index based on the id group in query editor side, then you can use Dax formula to use current index and id field values to find out corresponds value to calculate.

Create Row Number for Each Group in Power BI using Power Query - RADACAD

Regards,

Xiaoxin Sheng