Running total LY doesnt stop at last date

[Anonymous]

Hello,

I have an issue with the running total for Last Year... that doesn't stop running. I've looked for a whole day on the forum, tried lots of things but can not find the solution.

What I want to see is for a specific pickup month (in this case March2019, filtered on the left) to track the reservation pattern per booking calendar week next to the pattern of last year. The tricky part of my issue is: I am having two date tables 'Date Pickup' and 'Date Booked'. 

On the left you can see that I am filtering on pickup year and month from the date table ''Date Pickup'. In the table you see the WeekNumYear which is based on the date table ''Date Booked''. RezReporting is the main table with all information per reservation.

These two formulas work perfect as expected for the current year:

Res Nr = COUNTROWS(RezReporting)

Res Nr Cumulative =

    CALCULATE(

        [Res Nr],

        FILTER(

            ALLSELECTED(RezReporting),

            RezReporting[Date Booked 2] <=MAX(RezReporting[Date Booked 2])))

//'Date Booked 2' is the date booked column in the main table which is linked to the date table ''Date Booked''. I also have Date Pickup 2 in the RezReporting which is linked to the date table ''Date Pickup''.

But when I apply this to SAMEPERIODLASTYEAR I don't get a cummulative running total:

Res Nr LY =

CALCULATE(

[Res Nr],

    SAMEPERIODLASTYEAR(

        'Date Booked'[Date Booked]),

        SAMEPERIODLASTYEAR(

            'Date Pickup'[Date Pickup]))
//I have to filter on date booked AND date pickup as I have two date tables.

Res Nr LY Cumulative (Running) 2 =

CALCULATE(

    [Res Nr LY],

    FILTER(

        ALLSELECTED(RezReporting[Date Booked 2]),

        RezReporting[Date Booked 2]<=MAX(RezReporting[Date Booked 2])))

As you can see in the table, 'Res Nr LY Cumulative (Running) 2' does not result in a cummulative total.

So I tried this, which worked but then it doesnt stop running until the end of the date table.

Res Nr LY Cumulative (Running) =

CALCULATE(

    [Res Nr LY],

    FILTER(

        ALLSELECTED('Date Booked'[Date Booked]),

        'Date Booked'[Date Booked]<=MAX('Date Booked'[Date Booked])))

I thought to be clever by saying that if the Res Nr LY is blank, that the Res Nr LY Cumulative is also blank (see right ccolumn in table), but this is not a perfect solution because there will be blank gaps at the beginning of the table as well...

So to summarize: I need a filter so that my cumulative total doesnt keep on running but stops at the max date in the list. Anyone any suggestions? Thanks a lot! 

[d_gosbell]

This article shows how to prevent displaying future dates for cummulative calculations

https://www.sqlbi.com/articles/hiding-future-dates-for-calculations-in-dax/

[colourfullife]

Can you show me what and where to put in my measure?

if i want to show running total util the last day?

P running total in Date =

CALCULATE(

    SUM('Sheet1 (2)'[P]);

    FILTER(

        ALLSELECTED('Date'[Date]);

        ISONORAFTER('Date'[Date]; MAX('Date'[Date]); DESC)

    )

)

[d_gosbell · Accepted solution]

Sure, so I've not sure what the date column is called in your 'Sheet1 (2)' table, but to do this all in a measure the code would look something like the following:

P running total in Date =

VAR LastDayAvailable =

    CALCULATE (

        MAX ( 'Sheet1 (2)'[Date] );

        ALL ( 'Sheet1 (2)')

    )

VAR FirstDayInSelection =

    MIN ( 'Date'[Date] )

VAR ShowData =

    (FirstDayInSelection <= LastDayAvailable)

VAR Result =

    IF (

        ShowData;

        CALCULATE (

            SUM('Sheet1 (2)'[P]);

            FILTER(
              ALLSELECTED('Date'[Date]);
              ISONORAFTER('Date'[Date]; MAX('Date'[Date]); DESC)
           )

        )   

    )

RETURN Result

[Anonymous]

Thank you for your help!

I found a solution, a similar idea to what you propose by finding a min and max date and filter based on this. This is now my measure:

Res Nr Cumulative =

VAR Min_date = DATEADD(FIRSTDATE('Date Pickup'[Date Pickup]),-2,MONTH)

VAR Max_date = LASTDATE('Date Pickup'[Date Pickup])

RETURN

    CALCULATE(

        CALCULATE(

            [Res Nr],

            FILTER(ALL('Date Booked'),

                'Date Booked'[Date Booked]<=MAX('RezReporting'[Date Booked 2]))),

            FILTER('Date Booked','Date Booked'[Date Booked]>=Min_date

                &&'Date Booked'[Date Booked]<=Max_date))

[colourfullife]

Aweseme I got a desired result with your answer.

To get the aswer, we need 4 measrues, correct?

[d_gosbell]

---

@colourfullife wrote:

Aweseme I got a desired result with your answer.

To get the aswer, we need 4 measrues, correct?

---

I'm not sure what you mean here, the code above is one measure, it's just broken down internally into 4 variables. You could probably do the same thing in one large expression, but variables typically make it easier to break the logic into smaller parts so it's easier to read (and it often improves performance as variable values can be cached internally)