Running total Power BI

[mmowrey]

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])

)

)

[mmowrey]

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?

[Whitewater100]

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]

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

[Whitewater100]

Hi:

Yes it looks pretty good. Can your date table also connect to the Donor visit table?

Once your date table is made (it should have continuous datesto cover all your time and beyond (for projections) you can go to data view and click on table tools. You should see an option to "Mark as Date Table" say yes and choose you Date field as the field. It will tll you if you did it correctly. Now all the time intel measures will work for you.

I hope that helps!

[mmowrey]

can i link my date table to both donor visit and blood drive table? maybe thats why this isn't working still .. what table should i link to?

[amitchandak]

@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.

[mmowrey]

thats all i have .. In my date table i have day of week and in my goal table i have day of week..  my running total works until i add an 8th day.. then i keeps whatever the value for the 7th day and just keeps adding it along

[mmowrey]

can i attach a pbix..

[amitchandak]

@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]

still getting the same value after a certain amount of days