Forum Discussion

mmowrey's avatar
mmowrey
Icon for Helper I rankHelper I
4 years ago

Running total Power BI

I have a running total that stops calulating after 7 days and just inputs the last number continuously.. 

 

Here is my formula for running total:

Monthly Running Total Goal (RBC) =
IF(
[Red Cell Projected]<> BLANK(),
CALCULATE(
[Red Cell Projected],
FILTER(
ALLSELECTED(BloodDrive[DriveDate]),
BloodDrive[DriveDate]<= MAX(BloodDrive[DriveDate])
)
)

 

 

 

 

10 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    mmowrey , Do not check [Red Cell Projected] <> blank

     

    Try like, assuming [Red Cell Projected] is  measure 

    CALCULATE(
    [Red Cell Projected],
    FILTER(
    ALLSELECTED(BloodDrive[DriveDate]),
    BloodDrive[DriveDate]<= MAX(BloodDrive[DriveDate])))

     

    Better to use date table

     

    CALCULATE(
    [Red Cell Projected],
    FILTER(
    ALLSELECTED(Date[Date]),
    Date[Date]<= MAX(Date[Date])))

     

     

     


    To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

     

     

    • mmowrey's avatar
      mmowrey
      Icon for Helper I rankHelper I
      4 years ago

      still getting the same value after a certain amount of days

       

       

  • mmowrey's avatar
    mmowrey
    Icon for Helper I rankHelper I
    4 years ago

    In the table i am pulling from, there is no date.. Its just a day of week (1-7). I created a new measure (day of week) and linked those tables.  

     

    I fixed my formula as you suggested but did not fix issue.. Is it my date?

     

    • amitchandak's avatar
      amitchandak
      Icon for Super User rankSuper User
      4 years ago

      mmowrey , what else you have in the table other than the day of the week, to create an incremental id like date


      Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

    • Whitewater100's avatar
      Whitewater100
      Icon for Solution Sage rankSolution Sage
      4 years ago

      Hi:

      Once you get your date table connected to your fact table with the donanation info and marked as a Date Table you can use functions like this for your monthly totals or beyond. Below the date table is named DATES. You can also use DATESYTD, DATESQTD

      Month RT= CALCULATE ( 
   [Red Cell Projected]), DATESMTD (Dates[Date])
)

       

      • mmowrey's avatar
        mmowrey
        Icon for Helper I rankHelper I
        4 years ago

        I have created a dates table and linked as below.. DOes this look correct