Sum up Datediff based on filter

Question

Hi,&nbsp;hope someone can give me a hint for a problem I'm thinking about for a while now.&nbsp;I have Sales with the Date of Sales and who sold it:&nbsp;Table Sales:Date | Person | Amount&nbsp;I also have the date when the Person entered a new stage of experience:&nbsp;Table Person:Person | EndOfStage1 | EndOfStage2 | EndofStage3 | EntryDate | Manager&nbsp;Now I need to calculate the Amount per day in a certain stage for a selectable time period. The result should be in total or by Manager.So for Stage1 I need to sum up the amount of sales made by any Person in Stage 1 during the time of the saledivided by the sum of days all people spend in Stage1 during the selected Period.&nbsp;&nbsp;I especially have problems with the sum of days, because it is not just difference between EntryDate and EndOfStage1. For example when the upper bound of the date filter is set in the middle of these two dates, then the diff between EntryDate and the upper bound should only be counted.&nbsp;Also the calculation has to be made for each person and after that summed up to the respective context (eg Manager).&nbsp;Any clues on this?&nbsp;Best Regards and many thanks&nbsp;&nbsp;&nbsp;&nbsp;

amitchandak · Answer

I did not get it completely. But across the table need a context. You need to create a common person dimension. Or one of the tables is dimension.
Refer : https://community.powerbi.com/t5/Community-Blog/Decoding-Direct-Query-in-Power-BI-Part-2-Date-Difference-Across/ba-p/934397#M451
&nbsp;

v-yuta-msft · Answer

H3nning&nbsp;,
&nbsp;
I'm confused on your description. Could you please share some sample data and clarify more details about your expected result?
&nbsp;
Regards,
Jimmy Tao

h3nning · Answer

Hi,&nbsp;sorry for the confusuion. Person is a dimension of course (1:n).

h3nning · Answer

Only found a possibility to upload photos:&nbsp;The result is dependent on filter here. Lets say we select just a few days, 2020-02-29 until 2020-03-02.The result for Stage 1 and Manager Carl should be 0.5There were 2 sales for Carl's team in that period and in that stage (twice Simone and she was Stage 1).Carl's team members spend 4 days in that stage during that period (1 day Ute + 3 days Simone).So the result should be 2 divided by 4 for equals 0.5Hope that clears it up a bit. Thanks in advance 🙂&nbsp;Edit: result for Stage 2 should be 0, because no one made a sale during that period and in that Stage, but Ute spent there 2 days (0 devided by 2).&nbsp;Result for Manager Lilly and Stage 1 : 0 -&gt; no sales and 6 days -&gt; 0 / 6Result for Manager Lilly and Stage 2 : blank -&gt; no sales an no days 0 / 0

h3nning · Answer

amitchandak&nbsp;and&nbsp;v-yuta-msft&nbsp;, did this clarify?Best regards

Forum Discussion

Related content

DATEDIFF DAX

Using filter with DateDiff

dynamic x-axis based on filter

DATEDIFF with filter

DATEDIFF function with Filter