calculated column --> minutes to hours, days to hours, etc

[Anonymous]

Dear community members, 

I have a problem. I know that a part of the solution will be a calculated column, but I have no clue how to define it...

For each client we have a budget_volume and budget_unit, in other words, a certain number of hours, minutes, days, dayparts etc. But when I receive the invoice from my supplier, the the invoiced_unit is often not the same as the budget_unit. So I have a problem with making e.g. Budget vs Actual visuals. 

Does anyone how I can make a correct calculated column, that says: 

when budget_unit is the same as invoiced_unit, please state the invoiced_volume

when budget_unit is not the same as invoiced_unit, than: 

• budget_unit = minutes  &   invoiced_unit = hours  --> invoiced_volume * 60
• budget_unit = hours  &  invoiced_unit = minutes --> invoiced_volume / 60
• budget_unit = day part  &  invoiced_unit = hours -->  invoiced_volume / 4
• budget_unit = days  & invoiced_unit = hours --> invoiced_volume / 8

I attached a sample pbix to this post... https://we.tl/t-lNVA729jnT  

Hope to hear soon from you. 

Best regards, Sander

[Anonymous · Accepted solution]

Hi @Anonymous ，

Please correct me if I wrongly understood your question.

You want to compare Budget_unit and Invoiced_unit to decide the return value .

If the content of Invoiced_unit is same with Budget_unit , return Invoiced_volume .

If the content of Invoiced_unit is different with Budget_unit , Subject to Budget_unit and calculate Invoiced_volume , return the calculation result.

Column = SWITCH(TRUE(),'Table'[Budget_unit]="minutes" && 'Table'[Invoiced_unit]="hours",'Table'[Invoiced_volume]*60,

                        'Table'[Budget_unit]="hours" && 'Table'[Invoiced_unit]="minutes",'Table'[Invoiced_volume]/60,

                          'Table'[Budget_unit]="day parts" && 'Table'[Invoiced_unit]="hours",'Table'[Invoiced_volume]/4,

                            'Table'[Budget_unit]="days" && 'Table'[Invoiced_unit]="hours",'Table'[Invoiced_volume]/8,

                              'Table'[Budget_unit]='Table'[Invoiced_unit],'Table'[Invoiced_volume]

)

The effect is as shown:

Best Regards

Community Support Team _ Ailsa Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@Anonymous This works perfect! Thank you!

[sayaliredij]

You can try following steps

1. As I understand its required to generate both numbers in same unit so that they can be compared

2. Created 2 new columns using following formula

Budget_Common = Tabel[Budget_volume] * SWITCH(Tabel[Budget_unit],"hours",60, "Minutes",1,"days",60 * 24,"day parts",60 * 24)

Invoice_Common = Tabel[Invoiced_volume] * SWITCH(Tabel[Invoiced_unit],"hours",60, "Minutes",1,"days",60 * 24,"day parts",60 * 24)

Here I am converting both columns in same unit (Minutes)

Now you have following columns which you can use

Regards,

Sayali

If this post helps, then please consider Accept it as the solution to help others find it more quickly.