Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Same period previous N months

I want to calculate total sales for previous N months for the same period according to the slicer. Eg if in slicer I select range from 1-10-21 to 3-10-21 the my column chart should display total sales for previous N months within this range.

 

I wrote following Dax measure for the same but not getting desired result:

 

same period = CALCULATE(SUM(financials[ Sales]),DATESINPERIOD('Date'[Date],MAX('Date'[Date]), -'Select N'[N Value],MONTH),FILTER(financials,AND(financials[Date]>=MIN('Date'[Date]),financials[Date]<=MAX('Date'[Date]))))
 
Any suggestions are apprecited.
 
 
Need Assistance
Need Help
Need Help DAX
Need Help DAX measure

11 Replies

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

    Hi Anonymous 

    You can use the DATEADD function to move the period back a set number of months

    same period = 
VAR _SelectedN = SELECTEDVALUE('Select N'[N Value])
VAR _Result = 
CALCULATE(
    SUM(financials[Sales]),
    DATEADD('Date'[Date], -_SelectedN, MONTH)
)
RETURN
    _Result
    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      PaulOlding  if in slicer I select range from 1-10-21 to 3-10-21 the my column chart should display total sales for previous N months within this range. 

       

      I have already achieved result for total sales for previour N months

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

        Sorry, I don't understand.

        If the user selects 1-10-21 to 3-10-21 and previous 3 months, what dates should we be looking at?

         

        *Also, is 1-10-21 in d-m-yy or m-d-yy?

  • Icey's avatar
    Icey
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi Anonymous ,

     

    You can also create a measure like so:

    same period = 
VAR SelectedDates_ =
    VALUES ( 'Date'[Date] )
VAR SelectedDays_ =
    VALUES ( 'Date'[Day] )
RETURN
    CALCULATE (
        SUM ( financials[Sales] ),
        DATESINPERIOD (
            'Date'[Date],
            MAX ( 'Date'[Date] ),
            - 'Select N'[Select N Value] - 1,
            MONTH
        ),
        'Date'[Day] IN SelectedDays_,
        NOT ( 'Date'[Date] IN SelectedDates_ )
    )

     

     

    Best Regards,

    Icey

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

  • ankur1991's avatar
    ankur1991
    Regular Visitor
    2 years ago

    Is there any hardcode version in which you can see the sales amount of same period last month.

    for example, if i have data of 1 dec to 14 dec then it should display data of 1 nov to 14 nov.