Forum Discussion

vincenardo's avatar
vincenardo
Helper I
4 years ago
Solved

Create line chart that counts dates from two columns in data set and displays count by date

In my data set, I have two columns with dates. DteOnHold & DteOffHold

 

I want to create a line chart that counts the the number of dates for each column (DteOnHold & DateOffHold) by day.

I created a Stacked Area Chart and placed the DteOnHold and DteOffHold in the AXIS and Count of DteOnHold & Count DteOffHold in the Values - but this is not giving the correct results in the line chart.

 

Any suggestions?

 

Need Help
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    4 years ago

    Hi, vincenardo ;

    You could create two measures as follows:

    count of DteOnHold = CALCULATE(COUNT([DteOnHold]),FILTER(ALL('Table'),[DteOnHold]=MAX('Calendar Relative'[Date])))
    count of DteOnHold = CALCULATE(COUNT([DteOnHold]),FILTER(ALL('Table'),[DteOnHold]=MAX('Calendar Relative'[Date])))

    In addition , two tables don't have relationship.

    The final output is shown below:

    If not right ,can you share the output and more details  you want to achieve?


    Best Regards,
    Community Support Team_ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

6 Replies

    • vincenardo's avatar
      vincenardo
      Helper I
      4 years ago

      Thanks amitchandak -- I need some additional help, I am still a bit confused.

      I created a date table using this formula -

      Calendar Relative = CALENDAR(
      TODAY()-365,
      TODAY()+10
       
      Linked the DteOffHold to the Date in 'Calendar Relative' table (Made it active) and linked the DteOnHold to the same table.
       

      I am confused on what calculations I need to create next.

      Thanks!

       

       

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Community Support
    4 years ago

    Hi, vincenardo ;

    You could create two measures as follows:

    count of DteOnHold = CALCULATE(COUNT([DteOnHold]),FILTER(ALL('Table'),[DteOnHold]=MAX('Calendar Relative'[Date])))
    count of DteOnHold = CALCULATE(COUNT([DteOnHold]),FILTER(ALL('Table'),[DteOnHold]=MAX('Calendar Relative'[Date])))

    In addition , two tables don't have relationship.

    The final output is shown below:

    If not right ,can you share the output and more details  you want to achieve?


    Best Regards,
    Community Support Team_ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    • vincenardo's avatar
      vincenardo
      Helper I
      4 years ago

      For additional filtering, how can I modify the COUNT formulas so that I can use a slicer to filter the chart based on the Task Name?

      • v-yalanwu-msft's avatar
        v-yalanwu-msft
        Community Support
        4 years ago

        Hi, vincenardo ;

        If you have slicer, you could modify it.

        count of DteOnHold = CALCULATE(COUNT([DteOnHold]),FILTER(ALLSELECTED('Table'),[DteOnHold]=MAX('Calendar Relative'[Date])))

        change ALL() to ALLSELECTED().

        Best Regards,
        Community Support Team_ Yalan Wu