Forum Discussion

Ryaneb85's avatar
Ryaneb85
Regular Visitor
3 years ago

Cumalative Total - Cannot filter dates

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 wo...
Need Help
Ryaneb85's avatar
Ryaneb85
Regular Visitor
3 years ago

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's avatar
Greg_Deckler
Community Champion
3 years ago

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