Forum Discussion

andrewb95's avatar
andrewb95
Icon for Helper II rankHelper II
4 years ago

Understanding Time Functions - Currently and historically in a stage

I have a table which can be viewed with the following items

ID NumberDate StartedDate LostDate Completed Status
ABC01/01/2021nullnullOpen
ACB01/01/202101/02/2021nullLost
ADB01/01/2021null02/02/2021Won

 

The table offers that shown above, so you can see historically when the ID started the process, if they are still in the process or if they completed or lost the process. 

 

I want to have a matrix which will show me the following: 

DatesTotal Started ProcessTotal Lost in ProcessTotal WonCurrently Open
Jan10035047
Feb15005192
Mar100710275

 

As you can see I will now be able to see cumulative for how many are currently open based on how many are being added to the process each month. 

 

Please advise? 

Need Help

4 Replies

  • smpa01's avatar
    smpa01
    Icon for Community Champion rankCommunity Champion
    4 years ago

    andrewb95  you will need a Calendar table for this and once you have that you create active and inactive relationship with calendar and fact like this

     

     

    Once the initial set up is done, you can write following measures to give you what  you need and bring the axis from Calendar

     

    Completed =
CALCULATE ( COUNT ( 'fact'[Date Completed ] ) )


Lost =
CALCULATE (
    COUNT ( 'fact'[Date Lost] ),
    USERELATIONSHIP ( 'Calendar'[Calendar_Date], 'fact'[Date Lost] )
)


Started =
CALCULATE (
    COUNT ( 'fact'[Date Started] ),
    USERELATIONSHIP ( 'Calendar'[Calendar_Date], 'fact'[Date Started] )
)

Currently Open =
CALCULATE (
    CALCULATE (
        COUNT ( 'fact'[Date Started] ),
        FILTER (
            'fact',
            'fact'[Date Lost] = BLANK ()
                && 'fact'[Date Completed ] = BLANK ()
        )
    ),
    USERELATIONSHIP ( 'Calendar'[Calendar_Date], 'fact'[Date Started] )
)

     

     

     

    • andrewb95's avatar
      andrewb95
      Icon for Helper II rankHelper II
      4 years ago

      This is working for the following:

      • Completed 
      • Lost 
      • Started 

      However for Currently Opened, I wanna see the month on month opened. 

       

      MonthStartedLostCompletedOpen
      A10253
      B10058
      C5274

       

      Please advise how to get the latter value as in my data the formula does not work?

  • YukiK's avatar
    YukiK
    Icon for Impactful Individual rankImpactful Individual
    4 years ago

    You can have DAX measures like these and place them in a matrix visual:

    Total Started = COUNT(Table[Date Started])

    Total Lost = COUNT(Table[Date Lost])

    Total Won = CALCULATE( COUNT(Table[Status]), Table[Status] = "Won" )

    Total Open = CALCULATE( COUNT(Table[Status]), Table[Status] = "Open" )

     

    Hope that helps!