Cumalative Total - Cannot filter dates

[Ryaneb85]

Hi All,

I'm very green when it comes to both Power BI and Dax, but I'm attempting to put together a Dashboard which is currently pulling data via ODBC from our ERP system (SysPro). The link is working fine and date is being pulled into a table named AllOrders via a query.

One of the graphs I am trying to set up is a running/cumalative sales total for the current year, which seems to be working ok. 

Here is the chart I have for 2023:

Here is the table showing this data as expected:

All seems to be good and I have got here by using the following Dax I found on this forum by @Greg_Deckler (Edit: this isn't a copy and paste of Greg's code but used as guidance towards my solution. If it is wrong, that is all me!):

However, I want to add secondary Y-axis to the line chart to show the previous year/s sales across the same data range. To do this I plan to use the date variables as shown in the above, but this is where I seem to be hitting a brick wall.

If I change the __MaxDate variable to a DATE function similar to the __MinDate variable to test the principal, everythign melts down and the cumalative fucntion seems to stop working completely. See the example below:

It seems that it is now looking at all of the dates within said table, but also that an unknown/arbitrary number is showing as the value rather than the expected a cumaltive value.

At first I thought it may be beause the filter is not working and the results are not correct due to the large number of rows within this table, but if you remove the min date and keep the max date as a MAX as opposed to an entered date, it works just fine:

I've lost count of the amount of time I've spend googling and trying other solutions, so any help or suggestions that can be provided would be much appreciated!

P.S. I've tried using the built in quick measures but they do not work. The hot tips seems to suggest that the data resets itself everytime it sees a duplicate, and the data has multiple orders entered within the same day, I think it's just resetting itself each line. Not very handy!

Cheers!

[Greg_Deckler]

@Ryaneb85 Here is what is going on. Because you aren't grabbing anything in terms of the visual context but rather hard coding your date values, then every month is going to return the exact same value, the sum of the Value column between the 2 dates that you specified. So the ALLSELECTED or ALL function is overriding the visual context so that you can bring past dates into view. Thus, you need to do something like the following:

Better RT 3 = 
    VAR __MinDate = DATE(2023, 1, 1)
    VAR __MaxDate = DATE(2023, 4, 1)
    VAR __Date = MAX('Table'[Date])
    VAR __Table = FILTER(ALL('Table'), [Date] >= __MinDate && [Date] <= __Date)
    VAR __Result = IF(__Date > __MaxDate, BLANK(), SUMX(__Table, [Value]))
RETURN
    __Result

[Ryaneb85]

Hey @Greg_Deckler,

Many thanks for the quick reply!

I'll need to spend a little time reviewing the code and trying to figure out what is going on and why it works, but I've just dumped it into my measure and it works perfectly, so I'm over the moon already.

I'll mark it as a solution shortly, but I before I lose you I was hoping if you might be able to help with the next niggle where the lines on the graph are not running in 'parellel'. By this I mean I want the x-axis to be consistent (Jan, Feb, March etc.) and have the plotted lines to follow the month rather than year.

I'm not sure if that makes sense, but here is an image of the results as they are now, along with what I want

it to look like (my artwork is in blue):

Thanks,

Ryan

---

[Greg_Deckler]

@Ryaneb85  @Kishore_KVN is more or less correct. If you want 2023 data to show up in 2022 then you would to calculate the date shifts, for example something along the lines of:

Better RT 3 = 
    VAR __MaxDate = MAX('Dates'[Date])
    VAR __Date = DATE(YEAR(__MaxDate) + 1, MONTH(__MaxDate), DAY(__MaxDate))
    VAR __Table = FILTER(ALL('Table'), YEAR([Date]) = YEAR(__Date) && [Date] <= __Date)
    VAR __Result = SUMX( __Table, [Value] )
RETURN
    __Result

[Kishore_KVN]

For the previous year running total, you have to change variables 

Previous Year Running Total = 
Var __MinDate = Date("2022","1","1")
Var __MaxDateRef = Max('Table'[Date])
Var __MaxDate = DATE(YEAR(MaxDateRef)-1,MONTH(MaxDateRef),DAY(MaxDateRef))

Then use user variable for table and return with SUMX.

@Ryaneb85 If this post helps, then please consider accepting it as the solution to help other members find it more quickly.THANK YOU!!