topic Re: Running total Power BI in DAX Commands and Tips

Running total Power BI

mmowrey — Mon, 07 Feb 2022 15:16:33 GMT

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

)

Re: Running total Power BI

amitchandak — Mon, 07 Feb 2022 15:22:24 GMT

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.

Re: Running total Power BI

mmowrey — Mon, 07 Feb 2022 15:27:50 GMT

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?

Re: Running total Power BI

amitchandak — Mon, 07 Feb 2022 15:45:18 GMT

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.

Re: Running total Power BI

mmowrey — Thu, 10 Feb 2022 15:19:24 GMT

can i attach a pbix..

Re: Running total Power BI

Whitewater100 — Thu, 10 Feb 2022 16:01:40 GMT

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

Re: Running total Power BI

mmowrey — Thu, 10 Feb 2022 18:25:48 GMT

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

Re: Running total Power BI

Whitewater100 — Thu, 10 Feb 2022 19:08:46 GMT

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!

Re: Running total Power BI

mmowrey — Mon, 21 Feb 2022 21:26:50 GMT

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?

Re: Running total Power BI

mmowrey — Mon, 21 Feb 2022 22:22:34 GMT

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

Re: Running total Power BI

mmowrey — Tue, 22 Feb 2022 04:35:50 GMT

still getting the same value after a certain amount of days