Create visualization with generic date axis to compare number of filled data

[pedrohfornari]

I have a data source that contains tons of records, the creation date ("Aberto em"), and the resolution date ("Atendido em").

I'm creating a visualization to compare, month by month, the number of created records vs the number of solved records. So with this I can get some kind of "solution ratio", to see if my team is able to solve everything they get to do.

If I have "Aberto em" for 10 records in January of 2020, I want to get a bar sized by 10 in January

If I have "Atendido em" for 8 records in January of 2020 (without considering when it was created), I want to get a bar sized by 8 in January

The bars must have different colors.

I've got it semi-solved by creating an auxiliary table, but then I could not set some filters I wanted to apply to the visualization page, which are columns present on the original table.

Is there some way for me to create this view without using a new table?

Here is the visualization I'm looking for

[pedrohfornari · Accepted solution]

Hello guys!

Thanks for your answers. Using it I found the solution.

Using what @v-kelly-msft and @mahoneypat said, I was able to reach this:

First of all, I've created the relationships you guys recomended, but I was running into some problems ass well.

After looking for a solution a little bit more I found the issue. I was using fixed dates (one for each month) in the Meses data source, so I would never be able to map dates from Entregas to Meses. So I completely changed it.

My auxiliary table (Meses) now has only one column, using the function CALENDAR(start date, end date). Also I've created measures to get the Total of Entregas with Atendido em, and the total of Entregas with Aberto em. Than to get the balance I've created 2 other measures,

saldo mensal = [Abertas] + [Atendidas]

and

Saldo =

    CALCULATE(

        [saldo mensal],

        FILTER(

        ALL(Meses),

        Meses[Date]<=MAX(Meses[Date])

    )

)

Than the USERELATIONSHIP was the final cherry to my cake start working.

Thank you guys!

[v-kelly-msft]

Hi  @pedrohfornari ,

Glad to hear it.

Would you pls mark the reply as answered to let more people find it?

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

[v-kelly-msft]

Hi  @pedrohfornari ,

What kind of filters do you want to set?

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

[mahoneypat]

To do that, you can create two relationships between a date table and your table, and then use the USERELATIONSHIP function in one of your measures.

Using USERELATIONSHIP in DAX - SQLBI

Pat