Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Dan_Wis
Frequent Visitor

Measure for monthly cost based on daily allowance cost

Hello,

I have a problem with creating corect measure for calculating monthly cost - YTD for all employees

 

Emp NameStart DateEnd DateDaily cost
Emp 118-Jan-2008-Mar-2033.29
Emp 215-Jan-2019-Feb-2140.68
Emp 318-Feb-2005-Apr-2040.68
Emp 409-Feb-2023-Mar-2040.68
Emp 518-Jan-2008-Mar-2040.68
Emp 603-Jan-2019-Mar-2033.29
Emp 701-Mar-2031-Dec-2033.29
Emp 826-Jan-2019-Mar-2033.29
Emp 901-Apr-2031-Dec-2033.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,

 

1 ACCEPTED SOLUTION
7 REPLIES 7
Dan_Wis
Frequent Visitor

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

Dan_Wis
Frequent Visitor

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 Name31-Jan29-Feb31-Mar30-Apr31-May30-Jun31-Jul
Emp 1142980000
Emp 217193130313031
Emp 3012315000
Emp 4021230000
Emp 5142980000
Emp 62929190000
Emp 7003130313031
Emp 8629190000
Emp 900030313031



Total Cost:

 

Emp Name31-Jan29-Feb31-Mar30-Apr31-May30-Jun31-Jul
Emp 1466.06965.41266.320000
Emp 2691.56772.921261.081220.41261.081220.41261.08
Emp 30488.161261.08203.4000
Emp 40854.28935.640000
Emp 5569.521179.72325.440000
Emp 6965.41965.41632.510000
Emp 7001031.99998.71031.99998.71031.99
Emp 8199.74965.41632.510000
Emp 9000998.71031.99998.71031.99
Total:2892.296191.316346.573421.23325.063217.83325.06



thanks,

HI @amitchandak ,

 

That works!!! Many thanks for your support!! 🙂

 

Best regards,

v-gizhi-msft
Community Support
Community Support

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:

12.PNG

Hope this helps.

 

Best Regards,

Giotto Zhi

amitchandak
Super User
Super User

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/

amitchandak
Super User
Super User

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.