Forum Discussion

H3nning's avatar
H3nning
Icon for Helper V rankHelper V
6 years ago

Sum up Datediff based on filter

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 ...
Need Help
v-yuta-msft's avatar
v-yuta-msft
Icon for Community Support rankCommunity Support
6 years ago

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's avatar
    H3nning
    Icon for Helper V rankHelper V
    6 years ago

    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's avatar
      v-yuta-msft
      Icon for Community Support rankCommunity Support
      6 years ago

      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's avatar
        H3nning
        Icon for Helper V rankHelper V
        6 years ago

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