Forum Discussion

moseshewetson's avatar
moseshewetson
Frequent Visitor
9 years ago
Solved

Running total limit

Hello, I have a live running total for the month from our accounting system which i am having trouble with.   Here is the current formula:   Running total = IF( MIN('Date'[Date]) <= CALCULATE(M...
Need Help
  • v-huizhn-msft's avatar
    v-huizhn-msft
    9 years ago

    Hi moseshewetson,

    From your file, you have created the cline chart, which displays the data and running total before today. Based on your description :"Can someone help to modify the formula so it only displays values less than or equal to today's date?", I think you have got your target. While for "I need a way to filter out all transactions that are ahead of today's date so the running total line does not show dates ahead of time.", you does not show datea before today? It's confusing.

    You also can create your measure using the following formula.

    Running totalTest1 = 
    IF(FIRSTNONBLANK('Date'[Date],1)<TODAY(),CALCULATE(
     SUM(Sales[Amount]),
     FILTER(
     
      ALL('Date'), 'Date'[Date]<=MAX('Date'[Date])
    )))


    Please see the following formula, it only shows data and running total before today.



    Thanks,
    Angelia

moseshewetson's avatar
moseshewetson
Frequent Visitor
9 years ago

Hello v-huizhn-msft

 

The dotted line is just a trend line.

 

'Date' is a date table linked to a transaction table ('DR_INVLINES') that contains all of the sales transactions for the company.

 

I need a way to filter out all transactions that are ahead of today's date so the running total line does not show dates ahead of time.

 

Thanks.

v-huizhn-msft's avatar
v-huizhn-msft
Microsoft Employee
9 years ago

Hi moseshewetson,

Got it. Do you mind share some sample data, or .pbix file for further analysis. So we can post solution which is close to your requirement.

Thanks,
Angelia

    • v-huizhn-msft's avatar
      v-huizhn-msft
      Microsoft Employee
      9 years ago

      Hi moseshewetson,

      From your file, you have created the cline chart, which displays the data and running total before today. Based on your description :"Can someone help to modify the formula so it only displays values less than or equal to today's date?", I think you have got your target. While for "I need a way to filter out all transactions that are ahead of today's date so the running total line does not show dates ahead of time.", you does not show datea before today? It's confusing.

      You also can create your measure using the following formula.

      Running totalTest1 = 
      IF(FIRSTNONBLANK('Date'[Date],1)<TODAY(),CALCULATE(
       SUM(Sales[Amount]),
       FILTER(
       
        ALL('Date'), 'Date'[Date]<=MAX('Date'[Date])
      )))


      Please see the following formula, it only shows data and running total before today.



      Thanks,
      Angelia