cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Running total?

So I have a dates, hour, and goals query. 

 

A monthly goal is August "3000" hours, so my daily one is 96.77. I am trying to get my goals column to increase by 96.77 every day of August until it's 3000.

For example my goals table I have:
Month       | Goal |  Daily Goal
7/1/2017   | 4532 |  146.19
8/1/2017   | 4000 | 96.77

How could I get it to associate my daily goal with a date in Dates and add "96.77" to itself each day of the month?

5 REPLIES 5
Highlighted
Resolver II
Resolver II

Re: Running total?

If you are going to have a table\matrix that is by day, and you have your daily goal, you could just multiply the daily goal by the month day:

 

Get day of month: DAY('Calendar'[Date])

 

Get goal value rolleed up:

=DAY('Calendar'[Date]) * DailyGoal

Highlighted
Solution Sage
Solution Sage

Re: Running total?

 

Michellerosen,

 

I thought we had this sovled before....  What's new / different, or what are we missing that I can help with again?

 

https://community.powerbi.com/t5/Desktop/Make-monthly-goal-into-daily-goal/m-p/233447

 

Thank You,

Forrest

Highlighted
Frequent Visitor

Re: Running total?

The solution worked but only if I used the dates I made in the Daily Table. It wouldn't work with the dates in my Dates table, and for some reason when I added my dates in the Daily Table they would all be for the same day regardless of filter. 

Highlighted
Solution Sage
Solution Sage

Re: Running total?

Any chance you can share a copy of your PBIX file so we can review?  As you are looking for Day level detail, you'll have to have a Date table, but any Date table (as long as it has 1 entry for every date) should have worked just fine...?

 

FOrrest

Highlighted
Community Support
Community Support

Re: Running total?

Hi @michellerosen,

 

Did you means use target table to create the rolling total calendar?

If this is a case, you can refer to below sample.

 

1. Calculate column daily target:

Daily = [Total Goal]/DAY(DATE([Date].[Year],[Date].[MonthNo]+1,1)-1) 

9.PNG

 

 

2. Rolling target calendar based on target table.

CALENDAR = ADDCOLUMNS(SELECTCOLUMNS(CALENDAR(DATE(2017,1,1),TODAY()),"Date",[Date],"Daily Goal",LOOKUPVALUE(Records[Daily],Records[Date],DATE(YEAR([Date]),MONTH([Date]),1))),"Rolling",DAY([Date])*[Daily Goal])

10.PNG

 

If above not help, please share some sample data to analysis.

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors