Cumulative Sales for Full Previous Year

[Mike1402]

Sorry that I can see this question has been asked before, I've tried applying many of the different solutions but as I'm a complete beginner I'm worried I've lost sight of the answer through everything I've been trying...

Simply I would like a cumulative total for a given previous year. I have a DateTable set, a table of MonthlyData with SalesQty by date. I have managed to get a current year cumulative total working using the below

Running Qty YTD = 
If(
    Max(DateTable[Date])>TODAY(),BLANK(),
    Calculate(
        sum('Monthly Data'[Qty]),
        DATESYTD(DateTable[Date])
))

And I also have another table for the plan which I have also managed to get working cumulatively, but this is only for 2023

Running Plan YTD = 
Calculate(
    sum('Sales Plan'[Plan]),
    filter(ALL(DateTable),
    DateTable[Date] <= max(DateTable[Date])
    )
    )

I have tried a few methods, including one where I found a measure to get the total qty by month for the last year and used the below code, but this brings back the total year listed for every month

Running Qty PFY = 
   Calculate([Qty PFY],
    filter(all('DateTable'),
    DateTable[Date] <= max(DateTable[Date])
   )
   )

If I put QtyPFY into a table it doesn't show the values in a given year, only as a total so not sure if that's part of the reason ... I can't very easily share my data but would appreciate anyone telling me what I'm missing.

Thanks

[Mike1402]

Hi Rudy,

Appreciate you coming back and I know it's difficult to troubleshoot without the data. I've uploaded an example file to my onedrive which I downloaded from a YouTube video. It has some measures I was using in my file but I've altered it to show you what I'm attempting. Hopefully if we can get this working I can apply it to my file afterwards!

I have created a new "Page 1" and I've created a new measure "Units Sold CYTD" which is shown in the graph and the table alongside. This works as intended showing a cumulative total for the current YTD (in my file this is data from 2023 however in this example it appears to be showing the most recent year, I don't know how this function is supposed to work ...).

I've then tried to create a cumulative running total for the previous year, "Units Sold CPFY". In this example this should show the totals by month for 2014 as shown in the table, but instead it shows the result from the second table which is the sum total next to every month ..

I would like these measures to otherwise always be doing a running total for the current year and any specifed year if not just the previous, regardless of the current date.

Does this help?

Thanks

[Rudy_D · Accepted solution]

Hi,

Please use the formula below as a modification for 'Units Sold CPFY' measure. I used the SAMEPERIODLASTYEAR() function to take into account the row context of your table (months).

Below an example of your graph when I click on 2013.

If all is OK for you, please mark my answer as solution :).

Regards,

Rudy

Units Sold CPFY = 
    Calculate(
        sum(fSales[Units]),
        SAMEPERIODLASTYEAR(DATESYTD(dDate[Date]))
)

[Mike1402]

This is great thank you. If you have time I would appreciate it if you could explain how this works, why does this cumulate the total rather than my other measures which show the actual per month? Or if you know any good resources I can learn further that would be great.

Either way I appreciate you helping me with this 🙂

[Rudy_D]

Hi @Mike1402,

Sure, I will try to explain both Units Sold CYTD and Units Sold CPFY measures.

Units Sold CYTD 

This measure use CALCULATE function to sum the Units from fSales Table. You add a filter parameter using DATESYTD applied on the Date field from Date Table.

The important thing : this formula will filter your date field regarding the context (your page filters, visuals, etc.) and return the dates from the first date to the last day (which should be <= Today()) regarding the context !

So in your case, you used a graph with Months as X and your measure as Y (this is the context). So it will perform this calculation for each point of the X axis, taking the first day of the month to the last day as a filter context. If you don't filter on a particular year, the calculation is performed for the last year of your date table, for each month.

Units Sold CPFY

It is exactly the same ! But instead of taking the year of the context as a filter, it will take 'year of the context' - 1 (because we added the SAMEPERIODLASTYEAR() function in the filter parameter).

I hope this is more clear for you now :).

Rudy

[Rudy_D]

Hi Mike,

It's not easy to answer as we can't see the different fields you have in your tables ...

Did you try to use the PREVIOUSYEAR() function instead of DATESYTD() ?

Regards

Rudy