Forum Discussion

Foolke's avatar
Foolke
Helper I
6 years ago
Solved

count with dates

I have data similar to this:

TicketCreation_DateSolved_Date

14/05/201829/04/2020
28/05/201923/04/2020
317/07/201928/04/2020
428/04/202028/04/2020
520/04/202028/04/2020
625/04/202028/04/2020
722/10/201923/04/2020
825/10/201930/04/2020
927/04/202028/04/2020
1012/11/201929/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 !

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

4 Replies

  • 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.

       

       

    • Foolke's avatar
      Foolke
      Helper I
      6 years ago

      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's avatar
        az38
        Community Champion
        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