Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
I have a problem with creating corect measure for calculating monthly cost - YTD for all employees
Emp Name | Start Date | End Date | Daily cost |
Emp 1 | 18-Jan-20 | 08-Mar-20 | 33.29 |
Emp 2 | 15-Jan-20 | 19-Feb-21 | 40.68 |
Emp 3 | 18-Feb-20 | 05-Apr-20 | 40.68 |
Emp 4 | 09-Feb-20 | 23-Mar-20 | 40.68 |
Emp 5 | 18-Jan-20 | 08-Mar-20 | 40.68 |
Emp 6 | 03-Jan-20 | 19-Mar-20 | 33.29 |
Emp 7 | 01-Mar-20 | 31-Dec-20 | 33.29 |
Emp 8 | 26-Jan-20 | 19-Mar-20 | 33.29 |
Emp 9 | 01-Apr-20 | 31-Dec-20 | 33.29 |
I would need the total cost by month for all employees * number of days from start date till end of month * daily cost.
I have created separate calendar table and liked to "Start Date".
THank you,
Best regards,
Solved! Go to Solution.
@Dan_Wis , find the solution
https://www.dropbox.com/s/m13wtxau4d8jjr6/EmployeeMonthlyHours.pbix?dl=0
Appreciate your Kudos.
Hello,
Is there the other way around?
How can I calculate daily total cost for partucular day from Calendar table?
e.g.
...
04 March 20 = 8 Emp with total cost --> 33.29 + 40.68 +40.68 + 40.68 + 33.29 + 33.29 + 33.29 + 33.29 = 288.49
05 March 20 = 8 Emp with total cost --> 33.29 + 40.68 +40.68 + 40.68 + 33.29 + 33.29 + 33.29 + 33.29 = 288.49
...
...
...
31 December 20 = 2 Emp with total cost --> 33.29 + 33.29 = 66.58
Thank you,
best regards
Hi @v-gizhi-msft & @amitchandak
thank you for your support.
Unfortunately it does not work. the total of daily cost cumulates monthly:
No. of days within a month:
Emp Name | 31-Jan | 29-Feb | 31-Mar | 30-Apr | 31-May | 30-Jun | 31-Jul |
Emp 1 | 14 | 29 | 8 | 0 | 0 | 0 | 0 |
Emp 2 | 17 | 19 | 31 | 30 | 31 | 30 | 31 |
Emp 3 | 0 | 12 | 31 | 5 | 0 | 0 | 0 |
Emp 4 | 0 | 21 | 23 | 0 | 0 | 0 | 0 |
Emp 5 | 14 | 29 | 8 | 0 | 0 | 0 | 0 |
Emp 6 | 29 | 29 | 19 | 0 | 0 | 0 | 0 |
Emp 7 | 0 | 0 | 31 | 30 | 31 | 30 | 31 |
Emp 8 | 6 | 29 | 19 | 0 | 0 | 0 | 0 |
Emp 9 | 0 | 0 | 0 | 30 | 31 | 30 | 31 |
Total Cost:
Emp Name | 31-Jan | 29-Feb | 31-Mar | 30-Apr | 31-May | 30-Jun | 31-Jul |
Emp 1 | 466.06 | 965.41 | 266.32 | 0 | 0 | 0 | 0 |
Emp 2 | 691.56 | 772.92 | 1261.08 | 1220.4 | 1261.08 | 1220.4 | 1261.08 |
Emp 3 | 0 | 488.16 | 1261.08 | 203.4 | 0 | 0 | 0 |
Emp 4 | 0 | 854.28 | 935.64 | 0 | 0 | 0 | 0 |
Emp 5 | 569.52 | 1179.72 | 325.44 | 0 | 0 | 0 | 0 |
Emp 6 | 965.41 | 965.41 | 632.51 | 0 | 0 | 0 | 0 |
Emp 7 | 0 | 0 | 1031.99 | 998.7 | 1031.99 | 998.7 | 1031.99 |
Emp 8 | 199.74 | 965.41 | 632.51 | 0 | 0 | 0 | 0 |
Emp 9 | 0 | 0 | 0 | 998.7 | 1031.99 | 998.7 | 1031.99 |
Total: | 2892.29 | 6191.31 | 6346.57 | 3421.2 | 3325.06 | 3217.8 | 3325.06 |
thanks,
@Dan_Wis , find the solution
https://www.dropbox.com/s/m13wtxau4d8jjr6/EmployeeMonthlyHours.pbix?dl=0
Appreciate your Kudos.
Hi,
Please try to create this column first:
LeftDay * DailyCost = 'Table'[Daily cost]*(DATEDIFF('Table'[Start Date],DATE(YEAR('Table'[Start Date]),MONTH('Table'[Start Date])+1,1),DAY)-1)
Then create a measure:
Total Cost = SUMX(DISTINCT('Table'),COUNT('Table'[Emp Name])*'Table'[LeftDay * DailyCost])
Choose [Month] from seperate date table and this measure as a table visual, the result shows:
Hope this helps.
Best Regards,
Giotto Zhi
With a date calendar
CALCULATE(sumx(SUMMARIZE(filter(CROSSJOIN(Table2,Dim_Date),Dim_Date[Calendar_Date] >= Table2[StartDate] && Dim_Date[Calendar_Date]<= Table2[EndDate]),Dim_Date[Month Year],"_days",count(Dim_Date[date]),"_Max"max(Table2[ Daily cost])),[_days]*[_Max]),CROSSFILTER(Dim_Date[Calendar_Date],Table2[StartDate],None))
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 :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
Refer if this blog can help you to build the same: https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
Appreciate your Kudos.
User | Count |
---|---|
128 | |
108 | |
100 | |
64 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |