Forum Discussion

Richard_Halsall's avatar
Richard_Halsall
Icon for Helper IV rankHelper IV
3 years ago
Solved

Relative status compared to today

Hi

 

I am looking for some help to calculate previous and past statuses relative to todays date

 

1) the previous number of days relative to todays date a technician had a status of R

2) the future number of days relative to todays date a technician had a status of R

3) the date a technician has status of L relative to todays date

 

I have attached a pbix with the data tables and a mockup of the visual I need replicating if todays date was 16/08/2023

 

sample pbix 

 

If anyone can help I would be grateful

 

 

Need Help
  • parry2k's avatar
    parry2k
    3 years ago

    Richard_Halsall try the following measures and see if that is what you are looking for:

     

    Count Rows = COUNTROWS ( 'Table' )

Previous Days on R = 
CALCULATE ( 
    [Count Rows],
    Dates[Date] < TODAY (),
    'Table'[Status] = "R"
)

Future Days on R = 
CALCULATE ( 
    [Count Rows],
    Dates[Date] > TODAY (),
    'Table'[Status] = "R"
)

ON L = 
CALCULATE ( 
    MIN ( 'Table'[Date] ),
    'Table'[Status] = "L"
)

     

    👉 Learn Power BI and Fabric - subscribe to our YT channel - @PowerBIHowTo

    If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤️

2 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    3 years ago

    Richard_Halsall try the following measures and see if that is what you are looking for:

     

    Count Rows = COUNTROWS ( 'Table' )

Previous Days on R = 
CALCULATE ( 
    [Count Rows],
    Dates[Date] < TODAY (),
    'Table'[Status] = "R"
)

Future Days on R = 
CALCULATE ( 
    [Count Rows],
    Dates[Date] > TODAY (),
    'Table'[Status] = "R"
)

ON L = 
CALCULATE ( 
    MIN ( 'Table'[Date] ),
    'Table'[Status] = "L"
)

     

    👉 Learn Power BI and Fabric - subscribe to our YT channel - @PowerBIHowTo

    If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤️