Forum Discussion

aaa12's avatar
aaa12
Helper II
2 years ago

Challenging question - determine current state by month

Hello everyone, 

I have a challenging question. I have the table as below, with open date, closed date, and status...

 

My goal is to show a count of items that remain open during each month and create a graph like below:

 

So far the best approach I can think of is to subtract the closed date from the open date and create a matrix with a value of 1 for each month in between (screenshot below). BUT I can't figure out how to do that in DAX. Can anyone guide me here? or recommend a better solution?

 

 

Tips and Tricks

5 Replies

    • lbendlin's avatar
      lbendlin
      Super User
      2 years ago

      1. Do not use fake dates. Leave the close date empty and use COALESCE or a filter.

      2.  You are showing by month.  Does it mean you want to count which tickets were closed on any day of the month? 

      3. use a calendar table.

       

       

       

      • aaa12's avatar
        aaa12
        Helper II
        2 years ago

        lbendlin : Thank you...I wasn't able to reproduce it...

         

        1. Do not use fake dates. Leave the close date empty and use COALESCE or a filter. (DONE)

        2.  You are showing by month.  Does it mean you want to count which tickets were closed on any day of the month? (YES, just by month)

        3. use a calendar table. (DONE)

         

        My results....it seems the open and closed totals cancel each other out...

         

        My measures:

        Closed

        Open

        Running Total

         

        My modeling relationships:

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi,

    Thanks for the solution lbendlin  provided, and i want to offer some more information for user to refer to.

    hello aaa12 , based on your descriotion, you want to calculate the open items based on each month, you can refer to the following solution.

    Sample data 

    And i have a Calendar table, there is a 1:N relatinship relationship between tables, the key columns are open column in data table and date column in calendar table.

    1.In data table create a calculated column

    ClosedDate = IF([Closed]<[Open],MAXX('Calendar',[Date]),[Closed])

    2.Create a measure

    Open_count =
VAR _add =
    CALCULATETABLE (
        ADDCOLUMNS (
            'Table',
            "Flag",
                VAR a =
                    CALENDAR ( [Open], [ClosedDate] )
                VAR b =
                    VALUES ( 'Calendar'[Date] )
                RETURN
                    COUNTROWS ( INTERSECT ( a, b ) )
        ),
        CROSSFILTER ( 'Calendar'[Date], 'Table'[Open], NONE )
    )
RETURN
    COUNTROWS ( FILTER ( _add, [Flag] > 0 ) )

    Then create a visual, and put the date column and the measure to the visual.

     

    Output

     

    Best Regards!

    Yolo Zhu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    • aaa12's avatar
      aaa12
      Helper II
      2 years ago

      hello - thank you for the response. Unfortunately the totals were different than actuals using the proposed solution. I went with somewhat of an inelloquent solutions by creating separate columns to evaluate each time period and then graphing it together. It works but as mentioned before, it's not so elloquent. 

       

      Thank you again.