Switch Issue

[Anonymous]

Hello All,

I am currently trying to create a burndown style of report that is slightly complicated using the switch statement and comparing dates.

What I am trying to achieve:

• For each contractor know what amount of hours to bill for that month.
  • I need to bill the correct amount per month a contractor by month if their contract is considered active (Within the start and end date). 
    • To elaborate on this if the contractor / PO has not started I do not want to prematurely bill the contractor. 
      • For example a contractor works from December to May I do not want to bill from September to November or June to August.
  • Regardless of the contract status if the contractor has submitted hours use their submitted hours rather than the assumed amount per month.
  • Each month will need its own burndown amount; to expand further on this see below.
    • Contractor 1 
    • Total PO Hours: 2000
    • Sept Submitted Hours: 20 therefore September the column value should be 1980.
    • Oct Submitted Hours: 40 therefore October's column value will be 1940
    • Nov no hours were submitted but since it is december the contractor is assumed to work 140 hours. Therefore the November column would be 1800.

I have included an example excel file and an example PBI file. In the PBI file I have a few switch statements but the October statement is below so you can see an easy example without downloading anything. The switch statement below is not returning the expected value if the PO has already ended or has not started yet.

OctSwitchTest = SWITCH(TRUE(),

'Test Sheet 2'[Oct 2018] > 0 ,'Test Sheet 2'[Oct 2018],

'Test Sheet 2'[PO End Date]<format(10/31/2018,"Short Date"),184,

'Test Sheet 2'[PO Start Date]>format(10/31/2018,"Short Date"),-184)

The 184 vs -184 is to see what the output of the switch statement returns.

Excel file and PBI file:

Excel: https://app.box.com/s/yzg2tjiea7x34gepvjk4dtj1j30kliaj

PBI: https://app.box.com/s/3yzr3vog8kmljhyftgd3qpgthpzqho3y

Thank you all, I tried to be as thorough as possible with what issue I am facing as well as what I have tried.

[v-piga-msft]

Hi @Anonymous,

A little complex of your sceanrio.  What is your desired output? Your desired output will help me understand your scenario better.

In addition, your data sample have many columns, please note do not share any sensitive data in this forum. You could share the sample data which could reproduce the scenario. Simple data model will help use understand your scenario and logic more quickly.

Best Regards,

Cherry

[Anonymous]

Hi @v-piga-msft,

I appreciate the help ahead of time, I have been working on this issue for a little while now and I have been unable to figure it out. 

Essentially my desired output would be dependent on each individual employee but ultimately if an employee was able to work in a given month it would use a set amount of workable hours unless they have submitted hours worked. If they have submitted hours worked it would use that over the default value. I will give a few examples below.

Emp 1: Start Date = 9/1/18 | End Date = 8/31/19
This employee's hours value for every month would be the number of workable hours unless they have submitted their hours.

So Sept Worked Hours = 48.
If(Sept Worked Hours > 0, Sept Worked Hours, 152)

Emp 2: Start Date = 9/1/18 | End Date = 1/31/19

This employee would have workable hours only from Sept to January, unless they submitted hours after January for some reason.

So Feb Worked Hours = 48.
If(Feb Worked Hours > 0, Feb Worked Hours, 0)

Employee 3 and 4 are similar to employee 2. 

Emp 3: Start Date = 12/1/18 | End Date = 4/1/19

Emp 4: Start Date = 1/1/19 | End Date = 8/31/19

The issue I have been facing is due to the different start and end dates for each employee. This does not allow me to use a simple if or switch statement to solve the problem.

Aslo I appreciate the note about sensitive data, everything I have provided is dummy data that is to be used as sample data.

Best,

DataPepper