Forum Discussion

alexsr's avatar
alexsr
New Member
6 years ago

Figuring Out Calculations Based on Relative Dates

So here's my problem, I'll simply  the data set to the only things I need. Ultimately I'm trying to see how many hours are worked on a ticket, X days out from the ticket creation.

 

In table 1, I have tickets name and ticket created date.

In table 2, I have ticket name, hours worked, and worked date.


Or in other words:

 

table 1:

Fix Staging, 7/20/2020

Client Advocate, 7/15/2020

 

table 2

Fix staging, ,2 hours, 7/20/2020

Fix staging, 4 hours, 7/25/2020
Client Advocate, 7 hours, 7/24/2020

 

What I'm trying to get, is a formula in which I can ideally refrence a slicer to answer the question of "Show me how many hours were worked on a ticket within the first X days since the ticket was created." X ideally being something I can manually alter using a slicer on the page as opposed to within a formula.

 

Thanks!

 

Need Help

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    alexsr , ideally ticket should able to act as a master for ticket name.

     

    Few ways. One way is table 2 join with date table on worked date and with M-1 with ticket.

    And you can populate created date in Table2

     

    New column in table 2

    Created date = related(Table1[Created Date])

    Join this also with date table and with help from userelation active join in calculation

     

    https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

     

    Another way do not join Table1 and Table2, join both with common dimension like date.

    New column in table 2

    Created date = minx(filter(Table, Table1[Name]=table2[name]),table1[created date])

     

     

     

    • alexsr's avatar
      alexsr
      New Member
      6 years ago

      Excellent thank you, that worked! And what about referencing the X in a slicer of some sort? How do I do that?

      • v-kelly-msft's avatar
        v-kelly-msft
        Community Support
        6 years ago

        Hi alexsr ，

         

        Create a slicer table as below:

        Slicer Table = GENERATESERIES(1,30,1)

        Then create a measure as below:

        Measure = 
var _mindate=CALCULATE(MIN('Table 2'[Worked date]),FILTER(ALL('Table 2'),'Table 2'[ticket name]=MAX('Table 2'[ticket name])))
var _mindiff=DATEDIFF(MAX('Table 2'[Created date]),_mindate,DAY)
Return
IF(SELECTEDVALUE('Slicer Table'[Value])<_mindiff,0,SUMX(FILTER(ALL('Table 2'),'Table 2'[ticket name]=MAX('Table 2'[ticket name])&&'Table 2'[Worked date]<=SELECTEDVALUE('Slicer Table'[Value])+MAX('Table 2'[Created date])),VALUE(LEFT('Table 2'[hours worked],1))))

        And you will see:

        For the related .pbix file,pls see attached.

         

         
        Best Regards,
        Kelly
        Did I answer your question? Mark my post as a solution!