created and closed dates on same axis

[Charuta]

Hi Team,

I want to add a trend line for Open vs Closed bugs for last 1 year.

Open and Closed are 2 values in the [Status] column

Open bugs are tagged based on created date, whereas Closed is based on the resolution date. 

since these are 2 different dates, I was using Y-Axis and Secondary axis, with 2 measures created for Open and Closed

However we need this on one single axis. 

Can you please help?

this is how the visual looks like, but need this on single axis

[hnguy71 · Accepted solution]

Hi @Charuta 

You'll still need two measures, one for created and one for closed. But to use one axis, you'll need a date table. Connect the date table to your fact table with two relationships, one active and one inactive.

For the relationship that has an inactive relationship, let's say for example the closed date, your measure for the inactive relationship would require the USERELATIONSHIP function. Sample:

Closed = CALCULATE(SUM([TABLE]Column)), USERELATIONSHIP(DATE_TABLE[Date_Column], FACT_TABLE[Closed_Date]))

[Charuta]

thanks a lot, this worked!

[v-kpoloju-msft · Accepted solution]

Hi @Charuta,

Thank you for reaching out to the Microsoft fabric community forum. Also, thanks to @hnguy71, for his inputs on this thread. I reproduced the scenario, and it worked on my end. I used my sample data and successfully implemented it.

I am also including .pbix file for your better understanding, please have a look into it:

Hope this helps clarify things and let me know what you find after giving these steps a try happy to help you investigate this further.

Thank you for using the Microsoft Community Forum.

[hnguy71 · Accepted solution]

Hi @Charuta 

You'll still need two measures, one for created and one for closed. But to use one axis, you'll need a date table. Connect the date table to your fact table with two relationships, one active and one inactive.

For the relationship that has an inactive relationship, let's say for example the closed date, your measure for the inactive relationship would require the USERELATIONSHIP function. Sample:

Closed = CALCULATE(SUM([TABLE]Column)), USERELATIONSHIP(DATE_TABLE[Date_Column], FACT_TABLE[Closed_Date]))