Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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])))
Related Column = DIVIDE(RELATED('Master Data'[Column 2]),'Example Data'[Column5])Result:
Firstly 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
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])))
Related Column = DIVIDE(RELATED('Master Data'[Column 2]),'Example Data'[Column5])Result:
Firstly 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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
98 | |
81 | |
65 | |
62 |
User | Count |
---|---|
147 | |
116 | |
104 | |
88 | |
65 |