How to show comparing date in bar chart?

[wfeng]

Hi everyone,

I am working on a simple data set, like that:

When I put them in a bar chart, it will show like:

My setting is:

However, what I want is like this:

The issue is the date data was counted, but I cannot figure out how to discount it.

I will very much appreciate you if anyone can tell me how to do it. Thanks!

[amitchandak]

@wfeng , refer if this can help

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[wfeng]

Thanks for your advice! And you really did a good job, but I think they are not the same thing.

[v-yiruan-msft]

Hi @wfeng ,

I just created a sample pbix file for you, please check whether it is what you want.

Best Regards

Rena

[wfeng]

Hi Rena,

Thank you for creating a sample report for me!

Your Gantt is interesting, and I think it could be helpful. Initially, I want a chart as your bar chart, but I met the same issue, the x-axis is always counted by power bi. I just want the date is shown on the x-axis, for example, 1/1/2020, 2/1/2020, and so on. Do you have any idea?

By the way, your data structure is different from mine. That's a running data set with huge volume, and updated every week. It could be terrible if it needs to change the data structure every time. 

Please let me know if you figure out how to show date time on x-axis, I will very much appreciate it. Thanks again!

Best regards,

Weicong

[v-yiruan-msft]

Hi @wfeng ,

Sorry I haven't been able to find other suitable way to present your desired result except the previous method (change the table structure)...

@parry2k , @mahoneypat , Could you please help check this? You may have different idea on it... Thank you in advance.

Best Regards

Rena

[wfeng]

Hello Rena,

Never mind! Thanks for your help!

Best regards,

Weicong Feng

[mahoneypat · Accepted solution]

I would look through appSource visuals to see if something there meets your needs. Alternatively, you can trick an array into doing this by making a date table (unrelated to the data), a measure that compares the date with the completed/expected dates, and the conditional formatting in the background and font. See the following example, using the week column of a date table (you could use date, month, and so on) to get the result. It also uses the Show in Rows function of the array visual.

The measures used above are (replace BarChart with the actual name of the table)

Esperado. • var mindate - MIN('Date'[Date])

return if(mindate<-MIN(BarChart[Expected]),1)

Completado. • var mindate - MIN('Date'[Date])

return if(mindate<-MIN(BarChart[Completed]),1)

If this works for you, mark it as the solution. Praise is also appreciated. Please let me know if you don't.

Best regards

Pat

[wfeng]

Hello Pat,

Thanks for your constructive advice! From your chart, it is almost what I need. However, I am still confusing that how to make Power BI show the 1, 2, ... 23 on the x-asix? I need to create a separate table which includes one date columns, and create 2 measures as you shown. But I have no idea about conditional formatting on background. Would you mind explaining more detail for me?

Best regards,

Weicong

[mahoneypat]

Please see the attached pbix with the example. You will note that I made two measures - Expected. and Completed. to return a value of 1 if the Week in context (from the matrix columns) is <= the respective date (expected or completed). That value of 1 is returned to the cell and used to set conditional formatting or not (blanks are not formatted). Note that both the background color and font color have the same conditional formatting (so the 1 is not seen in the visual).

If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.

Regards,

Pat

[wfeng]

Hello Pat,

Thanks for your detailed explanation! Your solution is good enough for me!

Best regards,

Weicong