Running total of sales to-date considering duplicate sales data and filters

[MJEnnis]

Trading tips for kudos! 😄

I have the following situation:

I am working on a sales report for a tour operator. The time intelligence aspects have already been built around the “stay period”, that is, when the guests arrive and depart the site. But for sales data, I also need to consider the “booking date”, that is, when the vacation was purchased.

A further challenge is that all of the booking data is a stored in a table where each row refers to a booked unit, where the same booking may include multiple units, while the price column does not indicate a separate price for each unit rather the total price of the entire booking, i.e., duplicate price data. Thus, I have a situation where I need to use SUMMARIZE() + AVERAGE() in order to accurately sum the sales for any given period.

In addition, the table contains data also for canceled and unconfirmed bookings, whereas I am only interested in summing the confirmed bookings. So I need to use FILTER() to account for this and other rows which should be excluded from the total.

I could just create a new table for this purpose, but I do not want to over-complicate the model, since the project is for a third party.

I have successfully created a measure that calculates the total sales for the current “season”, which can be used in a card visual. That same measure also appears useful for calculating a trend of sales per day for the current season in a line graph. However, I want to be able visualize a running total of sales in a line graph. (And I eventually want to compare this trend year-over-year.) This is where I am stuck…

Here is the general structure of the data. 

Id	IdG	Season	DataAcquisizione	TotaleLordo	Stato	Unique Agenzia
1	333	2025	Wednesday, May 14, 2025	1150	confermato	202500145
2	333	2025	Wednesday, May 14, 2025	1150	confermato	202500001
3	334	2025	Tuesday, May 13, 2025	1908	confermato	202500001
4	334	2025	Tuesday, May 13, 2025	1908	confermato	202500011
5	335	2025	Tuesday, May 13, 2025	1519	confermato	202500101
6	336	2025	Tuesday, May 13, 2025	180	confermato	202500101
7	337	2025	Tuesday, May 13, 2025	154	confermato	202500000
8	338	2025	Tuesday, May 13, 2025	3450	confermato	202500502
9	338	2025	Tuesday, May 13, 2025	3450	confermato	202500001
10	338	2025	Tuesday, May 13, 2025	3450	confermato	202500724

Here is the measure that works for total sales to-date, and total sales each day. How can I do something similar to produce a running total through today?

Gross Sales Today = 

Var GST_ = SUMX(
                FILTER(
                    SUMMARIZE(
                    ALLSELECTED('Lista Preventivi e Prenotazioni e Cancellazioni'), 
                    
                    'Lista Preventivi e Prenotazioni e Cancellazioni'[IdG],
                    'Lista Preventivi e Prenotazioni e Cancellazioni'[Stato],
                    'Lista Preventivi e Prenotazioni e Cancellazioni'[Season],
                    'Lista Preventivi e Prenotazioni e Cancellazioni'[Unique Agenzia],
                    'Lista Preventivi e Prenotazioni e Cancellazioni'[DataAcquisizione]), 

                    'Lista Preventivi e Prenotazioni e Cancellazioni'[Stato] = "confermato" &&
                    'Lista Preventivi e Prenotazioni e Cancellazioni'[Season] = YEAR([Sales Refresh Date]) &&
                    NOT(LOOKUPVALUE('Lista Agenzie'[GruppoAgenzia], 
                        'Lista Agenzie'[Unique Agenzia], 
                        'Lista Preventivi e Prenotazioni e Cancellazioni'[Unique Agenzia]) 
                        IN {"HC", "Opzioni", "Garanzie"}) &&
                    'Lista Preventivi e Prenotazioni e Cancellazioni'[DataAcquisizione] <= [Sales Refresh Date]
                    ),

                    CALCULATE(AVERAGE('Lista Preventivi e Prenotazioni e Cancellazioni'[TotaleLordo])))

RETURN GST_

Any hints would be much appreciated!

[MJEnnis · Accepted solution]

@v-achippa 

I kept trying to get a measure to work. I like measures over calculated tables most of the time because they keep the model tidier and they often work better if you want to use filters.

But I couldn't resolve the issues above (especially the "not enough resources" issue). So I bit the bullet and created a new table where each row equals a sale. 

Sales = 

VAR T1_ = DISTINCT(SELECTCOLUMNS(
    
    FILTER('Lista Preventivi e Prenotazioni e Cancellazioni', 

        [Stato] = "confermato" &&
        NOT(LOOKUPVALUE('Lista Agenzie'[GruppoAgenzia], [Unique Agenzia], 'Lista Preventivi e Prenotazioni e Cancellazioni'[Unique Agenzia]) IN {"HC", "Opzioni", "Garanzie"}) &&
        [DataAcquisizione] <= [Sales Refresh Date]
        ),
                    
        "IDG", [IdG],
        "Season", [Season],
        "Date", [DataAcquisizione],
        "Gross Price", [TotaleLordo],
        "Net Price", [TotaleNetto],
        "YearsAgo", [Current Season] - [Season]
                        ))

VAR T2_ = ADDCOLUMNS(T1_,
        "X-Axis", DATE(YEAR([Date]) + [YearsAgo], MONTH([Date]), DAY([Date]))
                )

RETURN T2_

Then I created three measures for cummulative sales for this year and the previous two years.

This year: 

Sales This Year = 

VAR TY_ = [Current Season]
VAR MD_ = CALCULATE(MAX('Sales'[X-axis]), FILTER('Sales', 'Sales'[Season] = TY_))

VAR STY_ = Calculate(Sum('Sales'[Gross Price]), 
            FILTER(ALLSELECTED('Sales'), 
            'Sales'[Season] = TY_ && 
            'Sales'[X-axis] <= MD_))

RETURN STY_

Sales last season: 

Sales Last Year = 

VAR LY_ = [Current Season] - 1
VAR MD_ = CALCULATE(MAX('Sales'[X-axis]), FILTER('Sales', 'Sales'[Season] = LY_))

VAR SLY_ = Calculate(Sum('Sales'[Gross Price]), 
FILTER(ALLSELECTED('Sales'), 
'Sales'[Season] = LY_ && 
'Sales'[X-axis] <= MD_))

RETURN SLY_

And current season -2 for two years ago.

As you can see from the visualization below, it works quite well. You have earned your kudos. But I will leave this request open for another day or so in case you can reply with an efficient measure solution that accomplishes the same thing! 🙂

Thanks again!

[v-achippa]

Hi @MJEnnis,

Thank you for reaching out to Microsoft Fabric Community.

To create a running total of gross sales and to handle duplicate sales values per booking, you can use SUMMARIZE with AVERAGE. Here is the measure:

Gross Sales Running Total =

VAR MaxDate = MAX('DateTable'[Date])

RETURN

CALCULATE(

    SUMX(

        FILTER(

            SUMMARIZE(

                'Lista Preventivi e Prenotazioni e Cancellazioni',

                [IdG], [Stato], [Season], [Unique Agenzia], [DataAcquisizione]

            ),

            [Stato] = "confermato" &&

            [Season] = YEAR(MaxDate) &&

            NOT (

                LOOKUPVALUE(

                    'Lista Agenzie'[GruppoAgenzia],

                    'Lista Agenzie'[Unique Agenzia],

                    [Unique Agenzia]

                ) IN {"HC", "Opzioni", "Garanzie"}

            ) &&

            [DataAcquisizione] <= MaxDate

        ),

        CALCULATE(AVERAGE([TotaleLordo]))

    ),

    FILTER(

        ALLSELECTED('DateTable'[Date]),

        'DateTable'[Date] <= MaxDate

    )

)

DateTable should be your calendar table and it should be marked as a date table and have a proper relationship with Lista Preventivi e Prenotazioni e Cancellazioni [DataAcquisizione].

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thanks and regards,

Anjan Kumar Chippa

[MJEnnis]

What you propose is definitely on the right track!

The first problem is that I have 10 years of data in the data set. So setting the Season as the MAX('DateTable'[Date]) is problematic. It is resulting in all kinds of weird calculations when I drill down to months, weeks, etc. To focus on the year at hand, I have changed it to:

[Season] = YEAR('Last Refresh'[Sales Refresh Date])

The next problem is that my Calendar table runs until the end of the current year. So I get a straight line at the end of a line graph starting at today. That could be fixed by filtering the Calendar table to run through today only. But that cannot be done: while bookings are through today, most arrivals and departures are still in the future until late in any given season. So, I have to do something like this:

 VAR MaxDate_ = CALCULATE(MAX('Calendar'[Date]), FILTER('Calendar', [Date] <= [Sales Refresh Date])) 

Another problem is that the measure forces the calendar to start at January 1 of the current year, which I am still struggling to understand. But sales for the current season start in July of the previous calendar year.

The resulting graph is one that starts with sales well above zero at the start of the current year, but with the above adjustments, it is no longer flat through December and I can now drill down.

Finally, when I put [Date] on the x-axis, it is slow to load (and it would rarely load before filtering to focus only on the current season).  So, I am concerned that it might not load when I add measures for last season and season before that.

Any ideas on how to get it to show sales before January of the current year? (When I asjust the axis range, it behaves as if there is no data before January 1.)

Here is what the graph looks like with those changes above:

[MJEnnis]

Thanks a lot @v-achippa! I tried a couple variations of something similar before, including pluging a var and a measure into the standard running total DAX code. It didn't work for me. One gave wrong values and the other wouldn't load due to resources. But maybe because I wasn't using my calendar table. Will try again that way. As said in the post, the problem is that the active relationship between the calendar table and the bookings table is with the arrival and departure dates. But It should also work with the dates from the calendar table because there is an inactive relationship with the sales dates. However, in the end, I think I will have to create a new table for this for two reasons: 1) there are 10 years of sales data in the model and so resources will be an issue, 2) If I use different measures for each year, it will be hard to get them all on one line graph. Will let you know!

[MJEnnis]

Since there seem to be no takers so far, taggin some super users who have helped me solve much more difficult problems in the past! 

@Greg_Deckler 

@lbendlin 
@tamerj1 

[MJEnnis]

Meant to say I am trading kudos for tips!!!