Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Date measure

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.

 

 

Need Help

4 Replies

  • v-danhe-msft's avatar
    v-danhe-msft
    Microsoft Employee
    8 years ago

    Hi Anonymous,

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

     

    Regards,

    Daniel He

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      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's avatar
        v-danhe-msft
        Microsoft Employee
        8 years ago

        Hi Anonymous,

        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