Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

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.

 

 

4 REPLIES 4
v-danhe-msft
Microsoft Employee
Microsoft Employee

Hi @Anonymous,

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

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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.

 

End Date.PNG

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

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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:

Table.PNG

 

Thank you in advance for any ideas.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors