Figuring Out Calculations Based on Relative Dates

[alexsr]

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!

[amitchandak]

@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-tr...

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]

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]

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!