Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Create measure with relative date filtering?

Hello All

 

May I consult with you on how to create 2 Measures with relative date filtering?

 

1. Last 7-day Sales = Sales amount of 7 days before Invoice Date is Today()

2. Coming 7-day Sales = Sales amount of 7 days after today

 

Invoice DateSalesLast 7-Day Sales (May 11th to 17th)Coming 7-Day Sales (May 18th to 24th)
5/10/2022100  
5/11/2022200  
5/12/2022500  
5/13/2022400  
5/14/2022666  
5/15/2022800  
5/16/20221234  
5/17/2022500088004495
5/18/2022400  
5/19/2022500  
5/20/2022650  
5/21/2022890  
5/22/2022920  
5/23/20221000  
5/24/2022135  

 

Please kindly advise, thanks

 

Best regards

Fred

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous ,

     

    Please check the formulas.

    Last 7-Day Sales = CALCULATE(SUM('Table'[Sales]),FILTER(ALLSELECTED('Table'),'Table'[Invoice Date]>SELECTEDVALUE('Table'[Invoice Date])-7&&'Table'[Invoice Date]<=SELECTEDVALUE('Table'[Invoice Date])))
    Coming 7-Day Sales = CALCULATE(SUM('Table'[Sales]),FILTER(ALLSELECTED('Table'),'Table'[Invoice Date]>SELECTEDVALUE('Table'[Invoice Date])&&'Table'[Invoice Date]<=SELECTEDVALUE('Table'[Invoice Date])+7))

     

    Best Regards,

    Jay

3 Replies

  • rohit_singh's avatar
    rohit_singh
    Icon for Solution Sage rankSolution Sage
    4 years ago

    Hi Anonymous ,

    You will need to create two measures as shown below:

    Last 7-day Sales =

    var _currdt = SELECTEDVALUE('7-DayRolling'[Invoice Date])

    var _prevdt = _currdt - 7

    var _sales =
    CALCULATE(
    SUM('7-DayRolling'[Sales]),
    FILTER(ALLSELECTED('7-DayRolling'),
    '7-DayRolling'[Invoice Date]> _prevdt && '7-DayRolling'[Invoice Date] <= _currdt ))


    return
    if (_currdt = today(), _sales, blank())

     

    Next 7-day Sales =

    var _currdt = SELECTEDVALUE('7-DayRolling'[Invoice Date])

    var _nextdt = _currdt + 7

    var _sales =
    CALCULATE(
    SUM('7-DayRolling'[Sales]),
    FILTER(ALLSELECTED('7-DayRolling'),
    '7-DayRolling'[Invoice Date] <= _nextdt && '7-DayRolling'[Invoice Date] > _currdt ))


    return
    if (_currdt = today(), _sales, blank())

    These will give you the desired result

    Kind regards,

    Rohit


    Please mark this answer as the solution if it resolves your issue.
    Appreciate your kudos! ğŸ˜Š

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thanks Rohit

      I have created the 2 measures that you advised.

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous ,

     

    Please check the formulas.

    Last 7-Day Sales = CALCULATE(SUM('Table'[Sales]),FILTER(ALLSELECTED('Table'),'Table'[Invoice Date]>SELECTEDVALUE('Table'[Invoice Date])-7&&'Table'[Invoice Date]<=SELECTEDVALUE('Table'[Invoice Date])))
    Coming 7-Day Sales = CALCULATE(SUM('Table'[Sales]),FILTER(ALLSELECTED('Table'),'Table'[Invoice Date]>SELECTEDVALUE('Table'[Invoice Date])&&'Table'[Invoice Date]<=SELECTEDVALUE('Table'[Invoice Date])+7))

     

    Best Regards,

    Jay