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 h...
Need Help
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!