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 Number Date Started Date Lost Date Completed  Status ABC 01/01/2021 null null Open ACB 01/01/2021 01/02/2021 nu...
Need Help
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?