Date measure

[datavis]

I need to create a measure in DAX - I think - but I am not sure how to do it.

The name of my table is Sep-Jun Burn Down Report. I have columns for Jun 2018 Hours Worked, May 2018 Remaining Hours, Jun. 2018 Remaining Hours and End Date. 

If Jun 2018 Hours Worked > 0 then subtract from May 2018 Remaining Hours = Jun. 2018 Remaining Hours.

If Jun 2018 Hours Worked = 0 and End Date <  08/31/2018 then subtract 0 from May 2018 Remaining Hours = Jun. 2018 Remaining Hours.

If Jun 2018 Hours Worked = 0 and End Date is > 08/30/2018 then subtract 168 from May 2018 Remaining Hours = Jun. 2018 Remaining Hours.

Can someone help me write this DAX or direct me if I need to do something else? I hope you understand my explanation. I tried to be clear.

[v-danhe-msft]

Hi @datavis,

Could you please post me some pictures of your data and post me your desired result?

Regards,

Daniel He

[datavis]

Thank you Daniel for your response.

 
I have a bar chart for each month of the year beginning in September showing the hours remaining to work for each contractor. You will notice in the image that beginning in March, there are 914 hours remaining for this contractor. It is the same in March, April, May, June, and July. August drops because there are 168 hours available to work in August so the projected hours to work in August are subtracted from the remaining hours to work in July.

The reason there are 914 hours for this contractor for 5 months straight is because the contract ended. I would like to be able to show that the contract has ended and show 914 hours left so I thought I would fade the bars to show the contract ended.

I need a simple date table - I think - month, day and year - and create it using the End Date column in my original table. Is that the direction to go and how would I go about that? I hope this helps. Nothing but the best to you.

[v-danhe-msft]

Hi @datavis,

Could you please post me the data stracture in your row table, due to I could not figure out the data in your [Jun 2018 Hours Worked], [May 2018 Remaining Hours], [Jun. 2018 Remaining Hours] and [End Date] columns? And the dax formula you want to get is a measure or a calculated column?

Regards,
Danile He

[datavis]

I just found out that I cannot vary the tint of a color bar in the clustered column chart unless all bars are the same color and there is only one value in the Value field. I used different colors in the bars so the months would be different colors and stand out. Each remaining hours worked column is a separate value in the Values field so there are 12 values in the Values field.  Using 12 values and multi-color bars eliminates my ability to fade the colors in the bars in which the contract has ended.

Now I need to determine if showing months in which the contract has ended by fading the bar is more important than the multi-colored bars. I don't know if the fading bar is doable at this point unless I use a totally different approach to this problem. Any suggestions would be appreciated.

I used the columns named Sep Remaining Hours, Oct Remaining Hours, Nov Remaining Hours as the Value field.

This is the table I have:

Thank you in advance for any ideas.