distribute a value equally between two dates and represent on bar graph

[jackoftrades]

Hello Everyone,
i am new the powerbi but its fun learning and reading solutions here.

what i am unable to achieve is as below.

i have two tables - Date (which has only working weekdays)

second table is sales which number of hours required for each job.

this table looks like this

sales id || Hours || Value || Start Date || End Date

1           || 1000  || 10,00,000 || 21/12/20 || 12/12/24

2           ||  700   || 5,00,000   ||  31/01/21 || 13/05/23

3

4

so on, each row will have hours, value of the sales id ,start date and end date

i want a result in visualisation where i can see hours spread across the start date and end date. accordingly this should tell me i would need xxx hours for this month/day and i can look forward to understand the hours required to complete job
Thanks in advance 
@amitchandak i have seen you have written few queries around this but i am unable to get what i am looking for

[gmsamborn · Accepted solution]

Hi @jackoftrades 

It seems that I was using the WorkdayOffset column incorrectly.  It is supposed to be the opposite of NETWORKDAYS meaning that you would be able to answer a question like "What is the date 23 workdays from now?"   That's NOT what I needed in this scenario and never should have introduced it in the first place.  My mistake! 

In this case, I can simply use NETWORKDAYS.

WorkdayOffset - 4.pbix

Also, this version includes the End Date.

Again, my apologies for making this more complex that I had to.

[gmsamborn]

Hi @jackoftrades 

Should the end date of a range be included?

For example, if the date range was from Jan 15 to Feb 10, should Feb 10 be blank or should it be included in the calculation?

On the other hand, if the date range was Jan 1, 2023 to Jan 1, 2024, would you want it included?

Currently, it does NOT include the end date.

Let me know if you need this changed.

[gmsamborn]

Hi @jackoftrades 

I renamed [Distribution] measure to [Dist. (inner)] and added an additional measure:

Dist. (inner) = 
VAR _SelDt = MAX( 'Date'[Date] )
VAR _WkDay = MAX( 'Date'[Is Weekday] )
VAR _Result =
    IF(
        _WkDay = 0,
        BLANK(),
        CALCULATE(
            SUM( 'Table'[Per Workday] ),
            FILTER(
                ALLEXCEPT( 'Table', 'Table'[Sales ID] ),
                'Table'[Start Date] <= _SelDt
                    && 'Table'[End Date] > _SelDt
            )
        )
    )
RETURN
	_Result


Distribution = 
    SUMX(
        FILTER(
            'Date',
            'Date'[Is Weekday] = 1
        ),
        [Dist. (inner)]
    )

I hope this works for you.

WorkdayOffset - 3.pbix

[gmsamborn]

Hi @jackoftrades 

**********************

EDITED TO ADD: I didn't need WorkDayOffset in this situation.  It is supposed to be the opposite of NETWORKDAYS meaning that you would be able to answer a question like "What is the date 23 workdays from now?"  That's NOT what I needed in this situation and I shouldn't have included it.

I was able to replace it with NETWORKDAYS.

**********************

Would something like this help?

1. Build date table
   1. Add [Is Week Day] column.
   2. Add [WorkdayOffset] column.
2. For each [Sales ID]
   1. Find out how many WORK DAYS.
   2. Find out the value per WORK DAY.
3. For each WORK DAY
   1. Total EVERY [Sales ID]’s value for that WORK DAY

One of the main parts is creating a calculated column in my Date table for keeping track of workdays.  Take a look at the column after created and you'll see the purpose:  # of workdays since/to a fixed date.  

WorkdayOffset = 
VAR _Curr = [Date]
VAR _Table =
    FILTER(
        SUMMARIZE(
            ALL( 'Date' ),
            'Date'[Date],
            'Date'[Is Weekday]
        ),
        'Date'[Is Weekday] = 1
    )
VAR _Min =
    IF(
        _Curr < TODAY(),
        _Curr,
		TODAY()
    )
VAR _Max =
    IF(
        _Curr < TODAY(),
        TODAY(),
		_Curr
    )
VAR _Count =
    COUNTROWS(
        FILTER(
            _Table,
            [Date] >= _Min
                && [Date] <= _Max
        )
    ) - 1
VAR _Logic =
    IF(
        _Curr >= TODAY(),
        _Count,
        _Count * -1
    )
RETURN
    _Logic

After that, I can create a calculated column in my main table to calculate the number of workdays for each SalesID.

Workdays = 
VAR _Start = [Start Date]
VAR _End = [End Date]
VAR _StartOffset =
    CALCULATE(
        MAX( 'Date'[WorkdayOffset] ),
        'Date'[Date] = _Start
    )
VAR _EndOffset =
    CALCULATE(
        MAX( 'Date'[WorkdayOffset] ),
        'Date'[Date] = _End
    )
VAR _Result = _EndOffset - _StartOffset
RETURN
    _Result

After that I can calculate the [Per Workday] column by dividing [Value] by [Workdays].

Finally, a measure to show the distribution.

Distribution = 
VAR _SelDt = SELECTEDVALUE( 'Date'[Date] )
VAR _WkDay = SELECTEDVALUE( 'Date'[Is Weekday] )
VAR _Result =
    IF(
        _WkDay = 0,
        BLANK(),
        CALCULATE(
            SUM( 'Table'[Per Workday] ),
            FILTER(
                ALLEXCEPT( 'Table', 'Table'[Sales ID] ),
                'Table'[Start Date] <= _SelDt
                    && 'Table'[End Date] >= _SelDt
            )
        )
    )
RETURN
	_Result

Let me know if you have any questions.

WorkdayOffset - JoT.pbix

[jackoftrades]

Hello,
Thank you it works for me, i was able segregate the hours. you are a true solution sage.
what i dont understand from this is, why i am not able to see the data for month/year when i drill up in my bar graph?

[gmsamborn]

Hi @jackoftrades 

I'm not sure I can change it to be able to drill up since Date is needed on the x-axis.

If you want, you can wait to see if someone else can come up with a different solution.

Thanks,

Grant

[jackoftrades]

Yes, i have a date heirarchy in this date table, is it possible that it is not working in that heirarchy due the fact we have put the measures in the day alone?

[gmsamborn]

@jackoftrades 

I think I was able to convert it to a date table with Year, Month, and Date on the x-axis in the visual.  (I hope you weren't meaning the "Auto time-intelligence" hierarchies.)

I'm not sure if the totals are correct at the month or year level. 

Instead of a total, it is using the last day of the month / year (that is in context).

WorkdayOffset - 2.pbix

[jackoftrades]

Hello,
i tried this exactly before you kind solution workdayoffset -2 but when we drill up it doesnt show correct value for each month.

[lbendlin]

where i can see hours spread across the start date and end date

Be more precise.  Are both Start and End dates inclusive?  Is there a limit to the daily hours?  

 Date (which has only working weekdays)

Not good - Dates needs to be contiguous.  Use a workday column to distinguish between working days and non-working days.

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

[jackoftrades]

Sample data if it helps.
i dont the hours spread with hours basis, for example 90 hours spread across 1/1/20 - 31/3/20 which is 3 months or 90 days.
i need to spread the number of hours within these months or best between these number of days. i was trying to achieve this within number of working days alone which would be 64 days excluding weekends only and not public holidays.

so this is what i am looking for.

[Ashish_Mathur]

Hi,

Share the download link of the MS Excel file.  In another tab of the file, show the expected result using Excel formulas.  I will try to translate those formulas in the DAX language.

[lbendlin]

across 1/1/20 - 31/3/20 which is 3 months or 90 days.

It is only 90 days in non-leap years.  It is 91 days in leap years, including 2020.