How to calculate rolling sum of purchases based on selected date in slicer?

[jolson]

Hi all,

I need a rolling sum over a year. I want the rolling sum to update dynamically when I choose a different date from the slicer. I.e. if the user selects June 2023, the sum will be from June 2022 - June 2023. I've tried making a separate calendar from the selected date but the selected date kept returning blank for some reason. Here's the last thing I tried for the sum:

Rolling Purchases =
VAR SelectedDate = SELECTEDVALUE('TAShipments'[Date])
VAR MinDate = EDATE(SelectedDate, -12)
VAR MaxDate = SelectedDate

RETURN
CALCULATE(
SUM('TAShipments'[Purchases]),
FILTER(
ALLSELECTED('TAShipments'),
'TAShipments'[Date] >= MinDate && 'TAShipments'[Date] <= MaxDate
)
)

Nothing I've tried has worked. Can anyone help?

I should add that this is the code for the calendar:

Rolling Range =

VAR SelectedDate = SELECTEDVALUE('TAShipments'[Date])

VAR MinDate = EDATE(SelectedDate, -12)

VAR MaxDate = SelectedDate

RETURN

CALENDAR(MinDate, MaxDate)

When I throw that into a table, it only returns blank, even though when I make measures for each separate component, they return the expected results.

[v-yohua-msft · Accepted solution]

Hi, @jolson 

Perhaps you can calculate the sum of a year's rolling purchases directly from the slicer

How to Get Your Question Answered Quickly 

Best Regards

Yongkang Hua

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

[sevenhills]

I do not see any issues with the DAX.

Can you do one step at a time to check the values at each step and then try to identify where it is going wrong!

Assuming that you have used 'TAShipments'[Date] in your slicer!

1. See if you are getting the selected date correctly?

    this shows the selected value, no brainer here!

Rolling Purchases =
VAR SelectedDate = SELECTEDVALUE('TAShipments'[Date])
VAR MinDate = EDATE(SelectedDate, -12)
VAR MaxDate = SelectedDate

RETURN SelectedDate 

2. See if you are getting the EDATE correctly? this should show the 12 months behind date! 

Rolling Purchases =
VAR SelectedDate = SELECTEDVALUE('TAShipments'[Date])
VAR MinDate = EDATE(SelectedDate, -12)
VAR MaxDate = SelectedDate

RETURN MinDate 

3. Let us the dates are being reflected in the filter ... try for min first and then for max 

    this will tell the min (or max) date that is in the transaction table based on your selection! 

Rolling Purchases =
VAR SelectedDate = SELECTEDVALUE('TAShipments'[Date])
VAR MinDate = EDATE(SelectedDate, -12)
VAR MaxDate = SelectedDate

RETURN
CALCULATE(
  Min('TAShipments'[Date]), -- Max('TAShipments'[Date])
  FILTER(
      ALLSELECTED('TAShipments'),
        'TAShipments'[Date] >= MinDate && 'TAShipments'[Date] <= MaxDate
      )
)

4. Now the final measure, it should work!

   Replace with your original code, SUM('TAShipments'[Purchases]) , it should work.

5. optional check: go to the table view, select the table and filter the dates column between the dates and see if the data exists! it should! and see the total matches your step 4 output. 

[jolson]

This is what I get when I check each step:

As you can see, each step is giving the correct value, but the overall calendar is still returning blank.

[sevenhills]

Now that your dates and calculation in the measure is giving correct values. It is interesting why it is not showing the data. Could you share the pbix file? (remove all key information and personal identifiers before sharing)

Are you using the slicer from the Dim Date kind of table or from 'TAShipments'[Date]?

Do you see any rows in the optional step 5? (trying to see if the transaction table has data or not for the dates)

There is not that much logic exists! you have the correct dax in my view!