Creating Calculated Columns on basis of certain criteria

[Anonymous]

Hi all,

I have built out a report in excel using Pivot and Simple Vlookup functions now when I am replicating the same in Power Bi, I am facing a tough time in certain things. Since I am new to Power BI it would be great if you all can help me out.

Data Overview:- I have an excel file which has the data for different users who pass through certain mandatory steps. Each step has a start date and an end date. And Finally a date of Completion and the stating of the process for the user (Addendum Date) is also available.

Below is the screen shot of the data

 Here what the output will look like

I have used "Table" as a visualization in the Report.

I am able to calculate the "Time taken to complete the step" metric which is the difference between "StepCloseDateActual" and "StepStartDateActual".

Also done with "Total Duration" which can be calculated for Completed accounts as the difference between the "Completion Date" and "Addendum Date". For other accounts which are not complete it will be difference between Today() function and AddendumDate

As of now I have calculated them using "New Column" feature. My 1st querry is - Is there any other way through which I can calculate these mesures? if yes, would love to learn something new.

Now comes the tricky part - Every user will have a mandatory step of "Solution Provisiong" I need a cloumn which will calculate the difference between the Addendum Date and Solution Provisiong Step Closure Date. I am not able to figure it out on how i can get the calculations done. any suggestions?

Next issue is I have to calculate the difference between the step close date for Step "Imp. complete" and step start date for step "imp. start". and the value should come in a different Column.

Finally is there any what in which i can restrict the repetion of the label items in the table? and get an output like this?

[v-sihou-msft]

@Anonymous

- Is there any other way through which I can calculate these mesures?

If your calculation is based on row level data, you can simply create calculated column as you have done. You can also achieve it by using measure.

-calculate the difference between the Addendum Date and Solution Provisiong Step Closure Date. 

You need to create measure and filter the context for "Solution Provisiong". The formula can be like:

Diff Solution Provision=
CALCULATE ( MAX ( Table[ Addendum Date] ) )
    - CALCULATE (
        MAX ( Table[Step Closure Date] ),
        FILTER ( ALL ( Table ), Table[StepName] = "Solution Provisiong" )
    )

Same thing when calculating the difference between "Imp. complete" and "imp. start":

Diff between Start and End=
CALCULATE (
    MAX ( Table[Step Closure Date] ),
    FILTER ( ALL ( Table ), Table[StepName] = "Imp. complete" )
)
    - CALCULATE (
        MAX ( Table[Step Start Date] ),
        FILTER ( ALL ( Table ), Table[StepName] = "imp. start" )
    )

Regards,

[Anonymous]

tried the other measure too.

It seems like with this logic it is calculating the difference between the Max dates in each steps. however it should be calculated at each individual level. 

[Anonymous]

Thanks for the solution Simon 🙂

I did try to build the measure suggestted by you

Time To Go Live = CALCULATE ( MAX( ( RawData[Addendumdate] ) )
- CALCULATE (
MAX ( RawData[StageCloseDateActual] ),
FILTER ( ALL ( RawData ), RawData[StepName] = "Solution Provisiong" )
))

but the outcome is coming as a date. The data format field is Date/Time and since it is greyed out I can't convert it into numbers!! any possible way through which i can convert the output into days?