Calculate total for a range of dates outside of visual filters

[RandyShroyer]

Here is a screenshot of my visual:

The dark blue columns should be a running total of the light blue columns.  The green figures are the correct values for the second and third running total value but you can see that the running total is just the running total for each individual period.  I am trying to calculate the running by summing the transactions between the date chosen on the slider and the last date of each period.  So the second set of columns should be the sum of the transactions between 11/25/2020 and 01/02/2022.

Here is the Dax code for my running total column:

TESTRunningTotal =

CALCULATE (

                      SUM (

                                 factUtilization[ITEM_UNITS]

                              ),

                     ALL (dimDate[DayDate]),

                    FILTER (

                                 factUtilization,

                                 factUtilization[dteSERVICE_DATE] >= factUtilization[FirstDateInData]

                                 &&

                                 factUtilization[dteSERVICE_DATE] <= RELATED(dimDate[PayPeriodEndDate])

                               )

               )

FirstDateInData = CALCULATE(

MIN(dimDate[DayDate]),

ALLSELECTED(dimDate[DayDate])

)

Both are measures.

I added the ALL function (in orange) to my original code but it did not change a thing.  How do I get the Dax to start at the date selected in the slicer rather than the start date of the period without changing anything else?

Thanks

[v-stephen-msft]

Hi @RandyShroyer ,

From your screenshot, I see that you put the date period in your axis, and you also have a calendar table called dimDate, right?

Does your main table have a relational connection with the calendar table?

It's the best that you provide your sample data. Dummy data is also available.

Best Regards,

Stephen Tao

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

[RandyShroyer]

Thaks for your interest!

Yes the X axis is a date from our date table (dimDate).  And it does have a relationship with the fact table.

I have found a different way to achieve what I want by modifying a posted solution for a different problem.  But it required adding some extra columns and would prefer to calculate the running total "in" the visual.

[lbendlin]

Read about filter context. You use ALL() but then you replace that again with the measure's context. Using RELATED in measures is tricky.  Try something like this:

TESTRunningTotal =
var first = [FirstDateInData]
CALCULATE (SUM (factUtilization[ITEM_UNITS]),
           factUtilization[dteSERVICE_DATE] >= first,
           factUtilization[dteSERVICE_DATE] <= dimDate[PayPeriodEndDate]
          )

[RandyShroyer]

Thanks for your interest!  Unfortunately, your suggestion gave the same results as my own.

The running total always equals the amount in the individual periods rather than being a total of the current period activity and everything that comes after it.  The data is filtered by the X axis so I can't get at the data prior to the current period.

[RandyShroyer]

Sorry, that should be "everything that comes before it"