Forum Discussion
Running Total for workdays
I need some help about making a different calculation.
I have a dataset like that.
The first table shows how much spent did i for each day, and the second table shows what is the opening target for this month.
The target value only shows the value for first day of month. To calculatate other days target, substract the sum of OUTGO Values.
Example for this dataset to target calculation.
Target For TargetCalculated
1.1.2018 ---- 2445
2.1.2018 ---- 2445 - 34 = 2411
3.1.2018 ---- 2445 - 34 - 53 = 2358
4.1.2018 ---- 2445 - 34 - 53 - 48 = 2310
...
.
.
(The targets in the month table is completly independent from another table)
I want to make a calculation like that,
Firstly it will calculate for each date, how many workdays have after that till the end of month.
(The workdays logic is like that
| Monday | 1 |
| Tuesday | 1 |
| Wednesday | 1 |
| Thursday | 1 |
| Friday | 1 |
| Saturday | 1 |
| Sunday | 0,3 |
)
The example dataset for this calculation.
After
It will divide for each day the TargetCalculation and the remaining workdays value.
The expected values for January 2018 first 4 days,
Date NewCalculation
1.1.2018 ---- 86,7021 (2445/28,2)
2.1.2018 ---- 88,6397 (2411/27.2)
3.1.2018 ---- 90 (2358/26.2)
4.1.2018 ---- 91,6667 (2310/25.2)
Pbix File
Hi Anonymous,
Based on my test, you could refer to below steps:
Created two columns in 'Master Data' table:
Column = CALCULATE(MAX('Target Data'[TARGET]),FILTER('Target Data','Target Data'[MONTH]='Master Data'[Month]))Column 2 = [Column]-CALCULATE(SUM('Master Data'[OUTGO]),FILTER('Master Data','Master Data'[Date]<EARLIER('Master Data'[Date])&&'Master Data'[Month]=EARLIER('Master Data'[Month])))Create a relationship between below tables:Create a column in 'Example Data' table:Related Column = DIVIDE(RELATED('Master Data'[Column 2]),'Example Data'[Column5])Result:You could also download the pbix file to have a view.Regards,Daniel HeFirstly thank you for your reply.
It looks like fine but one case was missed.Actually i don't have ExampleData table. I was added it for show to 'column5' calculation logic.
I have to make this calculation in MasterData table.
In summary this solution is okey but i need to create a new column like 'Column5' in MasterData table.Hi Anonymous,
Based on my test, you could refer to below steps:
Enter a map table like below:
Create a week number column in 'Master Data' table:
Weeknum = WEEKDAY('Master Data'[Date],2)Create relationship between two tables:
Create below calculated columns:
Column 3 = RELATED(Map[value])
Column 4 = CALCULATE(SUM('Master Data'[Column 3]),FILTER('Master Data','Master Data'[Date]>=EARLIER('Master Data'[Date])&&'Master Data'[Month]=EARLIER('Master Data'[Month])))Result:
You could also download the pbix file to have a view.
Regards,
Daniel He
3 Replies
Hi Anonymous,
Based on my test, you could refer to below steps:
Created two columns in 'Master Data' table:
Column = CALCULATE(MAX('Target Data'[TARGET]),FILTER('Target Data','Target Data'[MONTH]='Master Data'[Month]))Column 2 = [Column]-CALCULATE(SUM('Master Data'[OUTGO]),FILTER('Master Data','Master Data'[Date]<EARLIER('Master Data'[Date])&&'Master Data'[Month]=EARLIER('Master Data'[Month])))Create a relationship between below tables:Create a column in 'Example Data' table:Related Column = DIVIDE(RELATED('Master Data'[Column 2]),'Example Data'[Column5])Result:You could also download the pbix file to have a view.Regards,Daniel HeFirstly thank you for your reply.
It looks like fine but one case was missed.Actually i don't have ExampleData table. I was added it for show to 'column5' calculation logic.
I have to make this calculation in MasterData table.
In summary this solution is okey but i need to create a new column like 'Column5' in MasterData table.Hi Anonymous,
Based on my test, you could refer to below steps:
Enter a map table like below:
Create a week number column in 'Master Data' table:
Weeknum = WEEKDAY('Master Data'[Date],2)Create relationship between two tables:
Create below calculated columns:
Column 3 = RELATED(Map[value])
Column 4 = CALCULATE(SUM('Master Data'[Column 3]),FILTER('Master Data','Master Data'[Date]>=EARLIER('Master Data'[Date])&&'Master Data'[Month]=EARLIER('Master Data'[Month])))Result:
You could also download the pbix file to have a view.
Regards,
Daniel He