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

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'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