Forum Discussion

mikeoshields's avatar
mikeoshields
Helper I
7 years ago
Solved

Running Totals

I know there are a plethora of running total posts but I haven't found an answer to my specific issue.  My running totals need to be based on the DateDiff field which is a measure.  Running1 is a cum...
Need Help
  • mikeoshields's avatar
    mikeoshields
    7 years ago

    Actually I found another solution that's a little simpler:

     

    Running1 = CALCULATE(
    SUM(wr_route[DateDiff),
    FILTER(
    ALLSELECTED(wr_route),
    wr_route[wr_lot]=MAX(wr_route[wr_lot]) &&
    wr_route[MaxDate]<=MAX(wr_route[MaxDate])
    )
    )
Ashish_Mathur's avatar
Ashish_Mathur
Super User
7 years ago

Hi,

This measure works for Running1

Running1 = SUMX(SUMMARIZE(CALCULATETABLE(wr_route,DATESBETWEEN(wr_route[MinDate],MINX(ALL(wr_route[MinDate]),wr_route[MinDate]),MAX(wr_route[MinDate])),DATESBETWEEN(wr_route[MaxDate],MINX(ALL(wr_route[MaxDate]),wr_route[MaxDate]),MAX(wr_route[MaxDate]))),wr_route[MinDate],wr_route[MaxDate],"ABCD",[Datediff]),[ABCD])

Someone else will help you with the second measure

mikeoshields's avatar
mikeoshields
Helper I
7 years ago

Thanks for the quick response.  Something I should have mentioned is that MinDate and MaxDate are measures not columns so the error I get is "Column 'MinDate' in table 'wr_route' cannot be found or may not be used in this expression".  The fact that these are measures is my biggest issue.  Thanks

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    7 years ago

    You are welcome.  You might as well share the link from where i can download your PBI file.

    • mikeoshields's avatar
      mikeoshields
      Helper I
      7 years ago

      Actually I found another solution that's a little simpler:

       

      Running1 = CALCULATE(
      SUM(wr_route[DateDiff),
      FILTER(
      ALLSELECTED(wr_route),
      wr_route[wr_lot]=MAX(wr_route[wr_lot]) &&
      wr_route[MaxDate]<=MAX(wr_route[MaxDate])
      )
      )