The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi Good day,
Im having trouble in distributing manhours diffirence each row on my table.
First I have a table auto generated by our system everyday and it will override on the following day and will override again on the following day and so on.
1. How can i get the diffirence manHrs of today data if yesterday data overide by today data?
Yesterday - Today = sum of Diffirence (A)
2. How can i distribute the sum of diffirence per row on my table.
Sum of Deffirence (A)/no. of day remaning (B) = (C)
Where no. of day remaining (B) = 3
2-13-24
2-14-24
2-15-24
so the outcome will be.
2-13-24 + C
2-14-24 +C
2-15-24 +C
Sample Table below.
Thank you in advance
Allan
Hi @AllanBerces ,
To calculate the difference in man-hours between two sets of data (yesterday's data and today's data), you would ideally need to have a historical record of the data before it gets overwritten. If your system does not store historical data, you might consider implementing a change to preserve each day's data snapshot. However, if you have access to historical data, you can use Power BI to compare the two datasets and calculate the difference.
And you can use these DAXs to create measures for calculating Total:
SUM_manHours 2 = SUMX(ALL('Table 2'), 'Table 2'[manHours])
SUM_manHours 1 = SUMX(FILTER(ALL('Table 1'), 'Table 1'[Category] = "A"), 'Table 1'[manHours])
Then use these DAXs to create new columns:
distribute =
VAR A = [SUM_manHours 1] - [SUM_manHours 2]
VAR B = COUNTROWS('Table 2')
RETURN
DIVIDE(A, B)
Current manHours = [manHours] + [distribute]
The final output is as below:
Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, Thank you very much on your reply.
it possible to distribute the manHrs on the following days after the current date to max date of my plan, let say in this case taday is Feb-13 so it will distribute from Feb-14 to 17. and the divisor 4 (Feb-14 to 17).
Thank you very much big help.
Hi @AllanBerces ,
Please change the distribute into this:
distribute =
VAR A = [SUM_manHours 1] - [SUM_manHours 2]
VAR B = TODAY()
VAR C = MAX('Table 2'[Date])
VAR D = DATEDIFF(B, C, DAY)
RETURN
DIVIDE(A, D)
The final output is as below:
Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
Any other way to add the increase of hour on my daily plan, if this process wont work.
Thank you and appreciated.
Hi Good day,
Some error on the current manhrs
Thank you
Allan
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
97 | |
80 | |
62 | |
57 |
User | Count |
---|---|
246 | |
119 | |
114 | |
86 | |
70 |