Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
My datasource is from a sharepoint task list that is synced with Project Online so all those values are available, I am just not sure where to start with applying the above rules.
Solved! Go to Solution.
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]))
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?
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?
Hi @nogi,
1. You need to import all the data into Power BI Desktop;
2. It seems you want to group the data by rules. Then you can add a calculated column with "Closed", "Current" and "Future".
3. Use the build-in functions. Maybe it looks like this:
varPopulation = VAR.P ( Sales[Quantity] )
The reports could be:
Rules varPopulation
Closed 0.88
Current 1.66
Future 2.1
If you have more questions, please provide your .pbix file.
Best Regards!
Dale
hi @v-jiascu-msft,
No pbix file as of yet as I want to build it this weekend, just trying to plan it out first. thanks for the idea but I am actually wanting the variance in the one column rather than 3 based on those rules. I have a project online schedule which I will be importing into the desktop client to do this.
so, just to clarify, I will have tasks - some already completed, some in progress and some not started (future tasks). The logic should be something like:
if task = closed then variance = actual close - baseline close else if task startdate <= today then variance = today - forecast date else variance = forecast date - baseline date endif
If closed, I want forumula 1 to be used, if open
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]))
I see now. I thought it was the variance in Statistics.
Best Regards!
Dale
User | Count |
---|---|
65 | |
27 | |
25 | |
17 | |
11 |