Forum Discussion

Foolke's avatar
Foolke
Helper I
6 years ago
Solved

count with dates

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...
Need Help
  • az38's avatar
    az38
    6 years ago

    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

az38's avatar
az38
Community Champion
6 years ago

Foolke 

what should be on X-Axis this chart?

give an example of desired result please

parry2k's avatar
parry2k
Super User
6 years ago

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.