Running Total that reacts to a date slicer - HELP!

[Brambilla80]

Hi all, I hope somebody can provide some guidance with this.

I want to display a cumulative curve on a histogram chart; I want the curve to react to a Date slicer (I can selected fiscal year, fiscal quarter, month) so that the starting point of the curve is 0 in correspondence of the start date of the period selected. Currently the cumulative curve is cumulating from the MIN date contained in the calendar and not the MIN date from the slicer selection (the only syntax I seem to be able to use to have a cumulative curve is the following).

CURRENT SYNTAX:

Cumulative Appointments =

    VAR SelectedPeriodStart = IF (ISFILTERED('Calendar'), MIN('Calendar'[Date]), BLANK()) -- this correctly returns the min date of the selected period on the slicer

    VAR SelectedPeriodEnd = IF (ISFILTERED('Calendar'), MAX('Calendar'[Date]), BLANK()) -- this correctly returns the max date of the selected period on the slicer

    RETURN

    CALCULATE (

         [SUM Estimate Fees GBP (k) APPOINTMENTS],

         FILTER (

              ALL('Calendar'[Date]),

              'Calendar'[Date] <= SelectedPeriodEnd

               )

    )

The issue is that the above returns a cumulative from the MIN date of the calendar, not the MIN date of the date selection from the slicer. I have then tried to use the following (and also a variation of this but using KEEPFILTERS - none of the attempts is working).

ATTEMPTED SYNTAX (not working):

Cumulative Appointments =

    VAR SelectedPeriodStart = IF (ISFILTERED('Calendar'), MIN('Calendar'[Date]), BLANK()) -- this correctly returns the min date of the selected period on the slicer

    VAR SelectedPeriodEnd = IF (ISFILTERED('Calendar'), MAX('Calendar'[Date]), BLANK()) -- this correctly returns the max date of the selected period on the slicer

    VAR SelectedDates =

          DATESBETWEEN(

              'Calendar'[Date],

              SelectedPeriodStart,

              SelectedPeriodEnd

          )

    VAR Result = CALCULATE ( [Cumulative Appointments], SelectedDates )

    RETURN

    Result

The above is not working.

Appreciate your help guys.

[Jihwan_Kim]

Hi,

I am not 100% sure without seeing the data model, but please try replaceing ALL with ALLSELECTED.

And please check if the calendar table is assigned as date-table.

Cumulative Appointments =
CALCULATE (
    [SUM Estimate Fees GBP (k) APPOINTMENTS],
    FILTER (
        ALL ( 'Calendar'[Date] ),
        'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
    )
)

[Brambilla80]

Annyeonghaseyo Jihwan Si (I lived in Korea - Busan)

The Date table is marked as date table (I have just checked). I have amended the syntax as per your recommendation but it didn't work (the cumulative curve defaults back to being a curve that connects the values of the histogram bars instead of cumulating them):

CALCULATE (

    SUM Estimate Fees GBP (k) APPOINTMENTS],

    ALLSELECTED('Calendar'[Date]),

    'Calendar'[Date] <= SelectedPeriodEnd

)

Would it help if I emailed you the .pbix file?

Thanks

[Brambilla80]

This is now working:

Cumulative Appointments =

VAR SelectedPeriodStart = IF (ISFILTERED('Calendar'), MIN('Calendar'[Date]),BLANK())

VAR SelectedPeriodEnd = IF (ISFILTERED('Calendar'), MAX('Calendar'[Date]),BLANK())

RETURN

CALCULATE (

[SUM Estimate Fees GBP (k) APPOINTMENTS],

FILTER( ALLSELECTED('Calendar'),

'Calendar'[Date] <= SelectedPeriodEnd

)

)

Thanks Mr. Kim! Appreciated your help!

[Jihwan_Kim]

Hi,

Thank you very much for your feedback.

I am not sure why mine did not work, perhaps I did not understood well your previous measure and the data model. It was quite difficult to understand 100% without seeing and checking it, however, in most cases when authors want to write measures that is responding to the slicer selection that is placed on the same page, ALLSELECTED  DAX function is generally used.