Forum Discussion

gemcityzach's avatar
gemcityzach
Icon for Helper IV rankHelper IV
2 years ago
Solved

Use Calendar for Time Intelligence on another Table with Multiple Dates

What do you think the optimal way to plot muliple dates from the same table would be in a combo column-line chart?   I have work units that have a 'Created' date and a 'Closed' date. I want to impl...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    2 years ago

    gemcityzach Yeah, doesn't work that way other than perhaps overlaying two visuals but that's a mess. Better to create a measure for the closed count of ids like this:

    Closed Count Measure = 
  VAR __Date = MAX('Date'[Date])
  VAR __Table = FILTER( ALL('Table'), [Closed Date] = __Date )
  VAR __Result = COUNTROWS( __Table )
RETURN
  __Result
gemcityzach's avatar
gemcityzach
Icon for Helper IV rankHelper IV
2 years ago

hey greg thanks for responding! those two options look nice but not quite what im looking for. sample data:

 

IDGroupEvent_TypeCreatedClosedDueDatePastDue
1Group1A;C03/01/2024null03/15/2024Yes
2Group2B 03/15/2024null03/30/2024Yes
3Group3A04/12/202404/18/2024 04/27/2024Yes
4Group1B;A 04/01/202404/18/202404/16/2024No
5Group1A;B;C 04/15/202404/18/202404/27/2024No

 

Column Chart X: Month-Year Created

Column ChartY: Count of IDs

Line X: Month-Year Closed

Line Y: Count of IDs

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
2 years ago

gemcityzach Yeah, doesn't work that way other than perhaps overlaying two visuals but that's a mess. Better to create a measure for the closed count of ids like this:

Closed Count Measure = 
  VAR __Date = MAX('Date'[Date])
  VAR __Table = FILTER( ALL('Table'), [Closed Date] = __Date )
  VAR __Result = COUNTROWS( __Table )
RETURN
  __Result
  • gemcityzach's avatar
    gemcityzach
    Icon for Helper IV rankHelper IV
    2 years ago

    Hey Greg that worked pretty well for me to convert it to a stacked bar chart. But now none of my on-page slicers work. I'm guessing I need to figure out a way to keep the 'Group' filter?

     

    Edit: I changed FILTER ( ALL ) to FILTER ( KEEPFILTERS ...) and that worked! 🙂 Do you know how I might get the X-Axis to show Month-Year instead of MMM-DD?

    • Greg_Deckler's avatar
      Greg_Deckler
      Icon for Community Champion rankCommunity Champion
      2 years ago

      gemcityzach That's typically done in your formatting for the column.

      • gemcityzach's avatar
        gemcityzach
        Icon for Helper IV rankHelper IV
        2 years ago

        Yeah I checked for that but it doesn't offer the ability to 'group' the data into months. There are just variations on MM-DD-YYYY. I used the SQIBI calendar template (DAX Date Template - SQLBI).

         

    • Greg_Deckler's avatar
      Greg_Deckler
      Icon for Community Champion rankCommunity Champion
      2 years ago

      gemcityzach Also, you could have used ALLSELECTED instead of ALL