Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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:
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?
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]) )
I have created a dates table and linked as below.. DOes this look correct
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!
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?
@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.
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
can i attach a pbix..
@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.
still getting the same value after a certain amount of days
User | Count |
---|---|
54 | |
23 | |
19 | |
18 | |
17 |
User | Count |
---|---|
91 | |
86 | |
46 | |
28 | |
22 |