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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Jbrunson09
Frequent Visitor

Value spread across dates and time based on calendar assignment table

Hello,

 

Pre warning is that I'm newer to DAX so my terminology might not be correct. But any help on solving my issue would be greatly appreicated as I couldnt find anything in previous posts.

 

The Goal:

To be able to spread cost values across dates per activity based on

  1. start date / finish date
  2. start time / finish time
  3. calendar assignment (Some may be 7 day workweek and some may be 6 day, 5 day, etc.)

In the end it should look something like this. notice how there is no values on the days that are non-working days for each activity which is defined by the calendar reltionship and its working / non working days

Jbrunson09_0-1659619488828.png

 

The Problem:

for the sample file I'm providing all activities work an 8 hour day (8am-12pm 1pm-5pm). the trick is that some activities start at 8am and others start at another time in the work day. So i have a cost per hour calculation but I need to be able to say theres only 5 hours in day 1 and 8 hours in days 2-4 and 3 hours on day 5 and then be able to multiply that by cost per hour to find out what each days total value is.

 

Here is a simple version in excel explaining some of the logic

Jbrunson09_4-1659621411218.png

 

 

I feel like this is a countrows type of problem or sumx where I need to count rows from the date table that

date>= Start && date<= Finish

&& [activities]clndr_id = [CALENDAR]clndr_id

&& [CALENDAR]day_count = 1 (ive added in a couln that gives 1 if its a working day and 0 if its a non working day)

 

But i dont know how to factor time into this. (same as day_count where 1 = working time and 0 = non working time) so you would need a 1 for day_count and a 1 for time_count to consider it a working day and time

Jbrunson09_2-1659620329298.png

 

 

Here is what I have been able to create but as you can see it is not calculating correctly

Jbrunson09_1-1659619867183.png

 

Here is a view of the model

Jbrunson09_3-1659620386707.png

 

 

Im hoping I have explained my problem well enough and that someone much smarter than I can help solve my issue. 

 

Any help is appreicated!! Thank you!

 

See attached my sample data file

PBIX Testing Data File 

1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

Kudos for taking on such a rather complex topic.  You're on the right track but you may want to clean up a bit. Too many dangly bits, too many extra visuals etc.

 

All you need to start is the Activities table and the (disconnected) Dates table.  Then you can process each interval the way you did it - Beginning day, intermediate days, ending day.  There are a lot of caveats here that we are going to ignore  (for example work starting on a weekend day or starting on a workday at 12pm, beginning and ending on the same day, exact weekend days etc)

Hour_Count_PD = 
switch(TRUE(),
-- beginning and ending on the same day
max(ACTIVITIES[Start_date_key])=max(ACTIVITIES[Finish_date_key]),DATEDIFF(min(ACTIVITIES[Start_time_key]),min(ACTIVITIES[Finish_time_key]),HOUR)-if(min(ACTIVITIES[Start_time_key])<time(12,0,0),1,0),
-- starting day hours
SELECTEDVALUE(DATES[Date])=max(ACTIVITIES[Start_date_key]),DATEDIFF(min(ACTIVITIES[Start_time_key]),TIME(17,0,0),HOUR)-if(min(ACTIVITIES[Start_time_key])<time(12,0,0),1,0),
-- ending day hours
SELECTEDVALUE(DATES[Date])=max(ACTIVITIES[Finish_date_key]),DATEDIFF(TIME(8,0,0),min(ACTIVITIES[Finish_time_key]),HOUR)-if(min(ACTIVITIES[Finish_time_key])>time(12,0,0),1,0),
-- in between days
SELECTEDVALUE(DATES[Date])>max(ACTIVITIES[Start_date_key]) && SELECTEDVALUE(DATES[Date])<max(ACTIVITIES[Finish_date_key]) && SELECTEDVALUE(DATES[Day of Week]) in {1,2,3,4,5},8)

see attached

View solution in original post

7 REPLIES 7
Jbrunson09
Frequent Visitor

Is anyone able to help me determine the best approach to solve this challenge / optimize what I currently have. When I load my smaller files, it takes awhile to load the data because I have used a calculated column. When I load my larger files it takes 1+ hour. What am I doing wrong and how can I optimize this?

 

The challenge is I need to keep it flexible to which days and hours to work based on what comes from the calendar table and which days to skip based on the exceptions table. Ive used multiple layers of sumx but I feel like thats a part of the issue in conjunction with the use of the complex calculated column (is there a way to keep this all in measure form?

 

Data Model:

Jbrunson09_0-1660763969870.png

 

Final result I'm looking for:

Jbrunson09_1-1660763999552.png

 

Any help is appreciated!!

 

Link to test file

lbendlin
Super User
Super User

Kudos for taking on such a rather complex topic.  You're on the right track but you may want to clean up a bit. Too many dangly bits, too many extra visuals etc.

 

All you need to start is the Activities table and the (disconnected) Dates table.  Then you can process each interval the way you did it - Beginning day, intermediate days, ending day.  There are a lot of caveats here that we are going to ignore  (for example work starting on a weekend day or starting on a workday at 12pm, beginning and ending on the same day, exact weekend days etc)

Hour_Count_PD = 
switch(TRUE(),
-- beginning and ending on the same day
max(ACTIVITIES[Start_date_key])=max(ACTIVITIES[Finish_date_key]),DATEDIFF(min(ACTIVITIES[Start_time_key]),min(ACTIVITIES[Finish_time_key]),HOUR)-if(min(ACTIVITIES[Start_time_key])<time(12,0,0),1,0),
-- starting day hours
SELECTEDVALUE(DATES[Date])=max(ACTIVITIES[Start_date_key]),DATEDIFF(min(ACTIVITIES[Start_time_key]),TIME(17,0,0),HOUR)-if(min(ACTIVITIES[Start_time_key])<time(12,0,0),1,0),
-- ending day hours
SELECTEDVALUE(DATES[Date])=max(ACTIVITIES[Finish_date_key]),DATEDIFF(TIME(8,0,0),min(ACTIVITIES[Finish_time_key]),HOUR)-if(min(ACTIVITIES[Finish_time_key])>time(12,0,0),1,0),
-- in between days
SELECTEDVALUE(DATES[Date])>max(ACTIVITIES[Start_date_key]) && SELECTEDVALUE(DATES[Date])<max(ACTIVITIES[Finish_date_key]) && SELECTEDVALUE(DATES[Day of Week]) in {1,2,3,4,5},8)

see attached

This was a big help! I went down a rabbit hole of SUMX which worked but wasnt very efficient and would lock up my larger dataset.

 

One thing I'm still struggling with is making this a calculated column. When I bring it to the column it doesnt add up correctly. it should match the 'remain_drtn_hr_cnt' column.

see here:

Jbrunson09_0-1659802810218.png

though it works find in a mtrix view seen here:

Jbrunson09_1-1659802857988.png

what am I doing wrong? I feel like its something simple but Im missing it.

 

P.S. Ive reworked your formula to take into account calendar type (7 day, 6, 5 etc. and I have added in an exceptions column to the 'DATES' table as I need it to skip holidays as determined by the calendar.

Jbrunson09_2-1659802992868.png

 

Many thanks for the help so far!

Link to Workbook

Sure, you could make that a calculated column, but that would mean you need to use a CROSSJOIN and would potentially use up a lot of storage.  Are you sure you want to go that route?

I suppose I dont want that. I'm ultimately trying to determine remaining Cost per hour and to show that in bar chart form by month / a matrix like my picture in my original post where by finding cost per hour I could then see how much cost was in a day.

Jbrunson09_0-1659826924152.png

So that matrix would show the daily remaining cost by activity and the bar chart would show the monthly total of all activities. I need to know I'm projecting to bill X amount in the next month and X amount the next month etc.

 

So if I can get this same result with a calculation and take up less storage than I'd love to know how to accomplish that.

 

column chart would look something like this style

Jbrunson09_1-1659827277248.png

 

That is an entirely different question. You are moving from day level granularity to month level, and from individual activities to all activities.  This means you will have to use a separate query for your measure, with aggregator functions. (ironically this would have been easier with calculated columns)

 

Note:  Please try to avoid using LookupValue.  There is a new function NETWORKDAYS() that you may want to use instead.

How would I use the networkdays function in this scenario because that is vital to make this work accurately on a larger dataset where I need to be flexible on which days to count / not count? Could I use the holidays exceptions in networkdays to exclude the dates that coincide with the calendar type in my exceptions column on the dates table? 

I have 2 calendar types in my new dataset that I uploaded where 1 is a 5 day workweek and 1 is a 7 day (uses proj_id-clndr_id 123456 for 7 day). Again though in the full data there can be 6 day, 4 day etc so how do I make the distinction in the networkdays based on that Id?

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors