Forum Discussion

nick9000's avatar
nick9000
Frequent Visitor
8 years ago
Solved

Filter Sales by a date

Hi,   I am looking to show sales information for employees three months after they have started. I have the start date in a table and have created a measure for the 3 month date or x month date:  ...
Need Help
  • nick9000's avatar
    nick9000
    8 years ago

    Awesome, Thanks Yggdrasill

     

    I made a couple of changes and seems to give the correct result. Two references to the sales table and the other two are to the employee table.

     

    3 month revenue =
    CALCULATE(sum(Sales[Amount]),
    DATESBETWEEN(
    Sales[Date],
    FIRSTDATE(Employee[Start Date]),
    eomonth(FIRSTDATE(Employee[Start Date]),2)
    )
    )

     

    Thanks

     

    Nick

Yggdrasill's avatar
Yggdrasill
Responsive Resident
8 years ago

Try DATESBETWEEN()

  • nick9000's avatar
    nick9000
    Frequent Visitor
    8 years ago

    thanks Yggdrasill, I couldn't get DATESBETWEEN to work

    • Yggdrasill's avatar
      Yggdrasill
      Responsive Resident
      8 years ago

      I tried your scenario

       

      This is what you need

       

      3M from startdate =
CALCULATE (
    SUM ( Table1[Amount] ),
    DATESBETWEEN (
        Table1[start date],
        FIRSTDATE ( Table1[start date] ),
        DATEADD ( FIRSTDATE ( Table1[start date] ), 3, MONTH )
    )
)

      Then create a visual table and insert employee and then this measure to get this

       

      employee & 3 month sale measure

      • nick9000's avatar
        nick9000
        Frequent Visitor
        8 years ago

        Awesome, Thanks Yggdrasill

         

        I made a couple of changes and seems to give the correct result. Two references to the sales table and the other two are to the employee table.

         

        3 month revenue =
        CALCULATE(sum(Sales[Amount]),
        DATESBETWEEN(
        Sales[Date],
        FIRSTDATE(Employee[Start Date]),
        eomonth(FIRSTDATE(Employee[Start Date]),2)
        )
        )

         

        Thanks

         

        Nick