Sum up Datediff based on filter

[H3nning]

Hi,

hope someone can give me a hint for a problem I'm thinking about for a while now.

I have Sales with the Date of Sales and who sold it:

Table Sales:

Date | Person | Amount

I also have the date when the Person entered a new stage of experience:

Table Person:

Person | EndOfStage1 | EndOfStage2 | EndofStage3 | EntryDate | Manager

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 sale

divided by the sum of days all people spend in Stage1 during the selected Period. 

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. 

Also the calculation has to be made for each person and after that summed up to the respective context (eg Manager).

Any clues on this?

Best Regards and many thanks

[H3nning]

@amitchandak and @v-yuta-msft , did this clarify?

Best regards

[v-yuta-msft]

@H3nning ,

I'm confused on your description. Could you please share some sample data and clarify more details about your expected result?

Regards,

Jimmy Tao

[H3nning]

Only found a possibility to upload photos:

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.5

There 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.5

Hope that clears it up a bit. Thanks in advance 🙂

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).

Result for Manager Lilly and Stage 1 : 0 -> no sales and 6 days -> 0 / 6

Result for Manager Lilly and Stage 2 : blank -> no sales an no days 0 / 0

[v-yuta-msft]

@H3nning ,

---

There 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.5

---

What does "Stage1" mean? Could you show the logic using some expression?

Regards,

Jimmy Tao

[H3nning]

@v-yuta-msft 

Thanks for your reply and sorry for the confusion.

That is just a column in my Data. Ute for example is considered in Stage 1 (it is an experience level) for all dates between her entry date and the date recorded for her end of stage 1 (EoS1 in the Data sample). So from 1st of August 2019 until 29th of February 2020 she is in Stage 1. She is considered stage 2 during the time between EoS1 and EoS2 and so on.

So far I managed to calculate the time each Person was in a particular Stage dependent on a selected period within a time slicer. Here for number of days in stage 1 during selected period:

__TimeTest =

VAR

StartVar =

IF(

CALCULATE(MIN(Timetable[myDate]);ALLSELECTED(Timetable[myDate]))<MIN('Person'[EntryDate])

;MIN('Person'[EntryDate])

;CALCULATE(MIN(Timetable[myDate]);ALLSELECTED(Timetable[myDate]))

)

VAR

EndVar=

IF(

CALCULATE(MAX(Timetable[myDate]);ALLSELECTED(Timetable[myDate]))>MIN('Person'[EoS1])

;MIN('Person'[EoS1])

;CALCULATE(MAX(Timetable[myDate]);ALLSELECTED(Timetable[myDate]))

)

RETURN

DATEDIFF(StartVar;EndVar;DAY)

For that I introduced a simple timetable with all dates on a daily basis which I use as dimension on the sales and for the slicer.

My next step is to figure out how to sum it up on manager level. Right now the result for the Manager Carl would be 3 and not 4. To get the right result it has to be calculated on the Person level and the summed up for the manager (1 from Ute + 3 from Simone)...

[amitchandak]

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-Differ...

[H3nning]

Hi,

sorry for the confusuion. Person is a dimension of course (1:n).