count with dates

[Foolke]

I have data similar to this:

TicketCreation_DateSolved_Date

1	4/05/2018	29/04/2020
2	8/05/2019	23/04/2020
3	17/07/2019	28/04/2020
4	28/04/2020	28/04/2020
5	20/04/2020	28/04/2020
6	25/04/2020	28/04/2020
7	22/10/2019	23/04/2020
8	25/10/2019	30/04/2020
9	27/04/2020	28/04/2020
10	12/11/2019	29/04/2020

I want to calculate for the last 14 calendar days the count of tickets created that date minus the count of tickets solved on that calendar day.

This I want to visualize in a column chart. 

How can I achieve this?

Thx !

[az38]

@Foolke 

what should be on X-Axis this chart?

give an example of desired result please

[parry2k]

@Foolke you need to add date dimension in your model and that is the first step, have a relationship with your date dimension to these two dates, one will be active relationship and others will be inactive relationship coz there can be only one active relationship between two tables. There are many posts on how to add date dimension in your model.

Create measures using COUNTROWS function and the one which has inactive relationship use USERELATIONSHIP function which make an inactive relationship to active.

Now you can view the data by date from date dimension using measures.

I hope it helps.

[Foolke]

Should look something like this:

X-axis: last 14 calendar days (start counting today)

Y-axis: (count of created tickets for date on X axis) minus (count of solved tickets on date on X-axis)

[az38 · Accepted solution]

Hello @Foolke

you can try to create a new table

New Table = 
ADDCOLUMNS(
CALENDAR(TODAY()-14, TODAY()),
"Count",
CALCULATE(COUNTROWS('Table'),'Table'[Creation_Date]=EARLIER([Date])) - CALCULATE(COUNTROWS('Table'),'Table'[Solved_Date]=EARLIER([Date]))
)

and use it for the visual bar