Comparing 3 date columns in same table and showing on stacked bar graph

[jriden37]

Hello Everyone,

I am new to this community and relatively new to PowerBi.  I am trying to build out a dashboard using a 100% stacked bar graph.

I have 3 columns in same table that I want to pull from.  These columns all have dates in them.  For each column some rows will have dates and others will not.  I want to be able to put all 3 columns into the graph each column with its own bar showing percentage of completed jobs compared to not complete.  I want to count as complete if there is a date and not complete if row is empy.  I have been able to do it with individual bar graphs for each column, but I would like to be able to add all 3 columns to one graph and just show them each on there own bar.  Showing both complete and not complete percentages on one bar

[v-tangjie-msft · Accepted solution]

Hi @jriden37 ,

According to your description, here are my steps you can follow as a solution.

(1) This is my test data. 

(2) We can create calculated columns.

Column_1 = IF(ISBLANK('Table'[column1]),"not complete","completed")

Column_2 = IF(ISBLANK('Table'[column2]),"not complete","completed")

Column_3 = IF(ISBLANK('Table'[column3]),"not complete","completed")

(3) We can create a measure. 

Measure 2 = 
var _a=COUNTROWS(FILTER(ALL('Table'),'Table'[Column_1]="completed"))
var _b=COUNTROWS(FILTER(ALL('Table'),'Table'[Column_2]="completed"))
var _c=COUNTROWS(FILTER(ALL('Table'),'Table'[Column_3]="completed"))
var _d=COUNTROWS(FILTER(ALL('Table'),'Table'[Column_1]="not complete"))
var _e=COUNTROWS(FILTER(ALL('Table'),'Table'[Column_2]="not complete"))
var _f=COUNTROWS(FILTER(ALL('Table'),'Table'[Column_3]="not complete"))
return IF(MAX('Table'[Column_1])="completed",DIVIDE(_a+_b+_c,_a+_b+_c+_d+_e+_f),DIVIDE(_d+_e+_f,_a+_b+_c+_d+_e+_f))

(4) Then the result is as follows.

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[v-tangjie-msft]

Hi @jriden37 ,

According to your description, here are my steps you can follow as a solution.

(1) This is my test data. 

(2) We can create calculated columns.

Column_1 = IF(ISBLANK('Table'[column1]),"not complete","completed")

Column_2 = IF(ISBLANK('Table'[column2]),"not complete","completed")

Column_3 = IF(ISBLANK('Table'[column3]),"not complete","completed")

(3) We can create a measure. 

Measure 2 = 
var _a=COUNTROWS(FILTER(ALL('Table'),'Table'[Column_1]="completed"))
var _b=COUNTROWS(FILTER(ALL('Table'),'Table'[Column_2]="completed"))
var _c=COUNTROWS(FILTER(ALL('Table'),'Table'[Column_3]="completed"))
var _d=COUNTROWS(FILTER(ALL('Table'),'Table'[Column_1]="not complete"))
var _e=COUNTROWS(FILTER(ALL('Table'),'Table'[Column_2]="not complete"))
var _f=COUNTROWS(FILTER(ALL('Table'),'Table'[Column_3]="not complete"))
return IF(MAX('Table'[Column_1])="completed",DIVIDE(_a+_b+_c,_a+_b+_c+_d+_e+_f),DIVIDE(_d+_e+_f,_a+_b+_c+_d+_e+_f))

(4) Then the result is as follows.

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[amitchandak]

@jriden37 , Create a date table, Join all three columns with date of date table, one join will be active others will be inactive. Use userealtionship to active the join in a measure

example

calculate( SUM(Sales[Sales Amount]),USERELATIONSHIP ('Sales'[CreateDate], 'Date'[Date]))

Use date from date table on visual

refer examples

Power BI: HR Analytics - Employees as on Date : https://youtu.be/e6Y-l_JtCq4
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
Power BI HR Active Employee Tenure Bucketing, and Hired, Terminated, and Active employees: https://youtu.be/fvgcx8QLqZU