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
Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
2 years ago

gemcityzach 
Take a look at these two Quick Measures as I think you want something like them.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364
https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365

 

If not, Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

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