2 date columns 2 status columns

[Anonymous]

Hi,

I have 2 staus columns i,e Current_Status & Approved_Status and I have separate date columns for each status as Current_Status_Date & Approved_Status_Date, I need to show both the staus in 2 different bar graphs and I should use a date column as a slicer, that date colum should be in month values.

Date	Number	Current_Status	Current_Status_Date	Approved_Status	Approved_Status_Date
01/01/2020	ABC123	In Review	01/03/2020	Approved	01/02/2020
01/02/2020	BCD123	Cancelled	01/04/2020
01/02/2020	DEF123	Remediated	01/03/2020	Approved	01/04/2020
01/03/2020	EFG123	Peniding Remediation	01/05/2020
01/03/2020	GHI123	Cancelled	01/05/2020	Approved	01/04/2020
01/04/2020	IJK123	In Review	01/05/2020
01/06/2020	LMN123	Risk	01/08/2020	Approved	01/05/2020
01/06/2020	MNO123	In Review	01/08/2020	Approved	01/05/2020
01/06/2020	NOP123	Remediated	01/08/2020	Approved	01/05/2020

[amitchandak]

@Anonymous , Not very clear. You can join both dates with the same date table and you will get active/inactive joins. You can activate a join using use relation.

See example -https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

[Anonymous]

@amitchandak ,

I was looking for the same requirment and I am following the same steps but still I dint get correct values, I should get 28 cancelled in the month of Aug.

[v-jingzhang]

Hi @Anonymous 

Is this what you required?

You can download this .pbix, hope it is helpful. 

Community Support Team _ Jing Zhang

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

[Anonymous]

@v-jingzhang ,

This what is my requirment, but when I am trying to the same on my orginal table I am not able to get the same values.

Attaching the pbix file.

https://drive.google.com/file/d/1BO987hAGdzK6a4FKjBcij-daFMKnRlCN/view?usp=sharing .

Example : In Aug 2020 cancle value should be 28 but I am not getting the same.

[v-jingzhang]

Hi @Anonymous , can you share the link again? It says I need access rights to this file when I try to open it. Thanks.

[Anonymous]

@v-jingzhang here is the link https://drive.google.com/file/d/1BO987hAGdzK6a4FKjBcij-daFMKnRlCN/view?usp=sharing

if you still get same error please request access as I dont know how to provide access to every1.

[v-jingzhang · Accepted solution]

Hi @Anonymous 

I create a new column to get the date of [Updated] column and use it to replace [Updated] column in the inactive relationship with Date table.

Updated Date = RADsNew[Updated].[Date]

Then I create a measure to count the number with USERELATIONSHIP function and put it in the chart as value. You can change the name of this measure as you like.

Measure = CALCULATE(COUNT(RADsNew[Number]),USERELATIONSHIP(Dates_Table[Date],RADsNew[Updated Date]))

Please download the pbix here.

Regards,

Jing