Forum Discussion

nogi's avatar
nogi
Advocate II
8 years ago
Solved

Calculating true variance

I have a logic in my head which I am trying to work out the best way to implement using Power BI. I want to show variance (in days) for tasks using the following rules: Closed - Actual Finish - Bas...
Need Help
  • nogi's avatar
    nogi
    8 years ago

    For the moment, I have a calculate filed in project that gives me the value required:

     

    IIf([% Complete]<100,IIf([Finish]>now(),DateDiff("d",[Baseline Finish],[Finish]),DateDiff("d",[Baseline Finish],now())),DateDiff("d",[Actual Finish],[Baseline Finish]))
  • nogi's avatar
    nogi
    8 years ago

    I played around some more and have found it easier to do the same with DAX:

     

    Column: CheckVariance = IF(ISBLANK('Tasks'[TaskActualFinishDate].[Date]),
	IF(TODAY()>'Tasks'[TaskFinishDate].[Date],TODAY()-'Tasks'[BaselineFinishDate].[Date],
	'Tasks'[TaskFinishDate].[Date] - 'Tasks'[BaselineFinishDate].[Date]),
	'Tasks'[TaskActualFinishDate].[Date] - 'Tasks'[BaselineFinishDate].[Date])

    Now, what if I only wanted to know the business days and exclude weekends from the count?

nogi's avatar
nogi
Advocate II
8 years ago

For the moment, I have a calculate filed in project that gives me the value required:

 

IIf([% Complete]<100,IIf([Finish]>now(),DateDiff("d",[Baseline Finish],[Finish]),DateDiff("d",[Baseline Finish],now())),DateDiff("d",[Actual Finish],[Baseline Finish]))
v-jiascu-msft's avatar
v-jiascu-msft
Microsoft Employee
8 years ago

I see now. I thought it was the variance in Statistics.

 

Best Regards!

Dale