Creating a monthly/quarterly table in powerBI based on a single row input

[scott_hsd]

Hi I have an excel spreadsheet that only has the number of hours (either per month/quater or even once as a frequency) with a start date and end date per client. I want to expand this in powerBI to be the cumulative hours for each frequency by client so I can then compare the actual hours by month from another table to increase/decrease the number of hours as appropriate. Any help or ideas would be great.

Thanks

Scott

Client	Type	Hours	Frequency	Start	Expiry
Client 1	Contract	15	Monthly	1/07/2021	31/12/2021
Client 2	Contract	40	Once	1/07/2021	30/06/2022
Client 3	T&M	0	None	1/07/2021	30/06/2022
Client 4	T&M	0	None	1/07/2021	30/06/2022
Client 5	Contract	38	Once	1/07/2021	2/08/2022
Client 6	Contract	80	Monthly	1/07/2021	30/06/2023
Client 7	Contract	120	Monthly	1/07/2021	30/06/2022
Client 8	Contract	60	Monthly	1/08/2021	30/06/2022
Client 9	Contract	15	Monthly	1/07/2021	30/06/2022
Client 10	T&M	0	None	1/07/2021	31/03/2022
Client 11	T&M	0	None	1/07/2021	22/01/2022
Client 12	T&M	0	None	1/07/2021	30/06/2022
Client 13	Contract	160	Once	1/07/2021	21/08/2022
Client 14	Contract	40	Monthly	1/07/2021	30/07/2022
Client 15	Contract	60	Quarterly	1/07/2021	30/06/2022
Client 16	Contract	60	Quarterly	1/07/2021	30/06/2022
Client 17	Contract	60	Quarterly	1/07/2021	30/06/2022
Client 18	T&M	0	None	1/07/2021	23/07/2022
Client 19	T&M	0	None	1/07/2021	30/06/2022

[scott_hsd]

Hi all, just to clarify further on this, what I really want to do is create a new table where the table is only populated with the logged hours from another table for the date of the logged hours where this date falls in between the start and expiry date of another table.

eg. Table A has the start and Exipry date for each ProjectID

Table B has the hours logged for each date for each ProjectID

Table C (new table) needs to have only the logged hours from table B that are between the dates in table A for each ProjectID

Each table has a key identifier which is ProjectID

Any Help please

[scott_hsd]

Yes thanks

[Icey]

Hi @scott_hsd ,

If you want to use DAX, try to create a calculated table and a calculated column like so:

DAX method = 
VAR Date_ =
    FILTER (
        CALENDAR ( MIN ( 'Table'[Start] ), MAX ( 'Table'[Expiry] ) ),
        DAY ( [Date] ) = 1
    )
RETURN
    FILTER (
        CROSSJOIN ( 'Table', Date_ ),
        SWITCH (
            [Frequency],
            "None", [Date] = [Start],
            "Once", [Date] = [Start],
            "Monthly",
                [Date] >= [Start]
                    && [Date] <= [Expiry],
            "Quarterly",
                [Date] >= [Start]
                    && [Date] <= [Expiry]
                    && MONTH ( [Date] ) IN { 1, 4, 7, 10 }
        )
    )

cumulative hours = 
CALCULATE (
    SUM ( 'DAX method'[Hours] ),
    FILTER (
        ALLEXCEPT (
            'DAX method',
            'DAX method'[Client],
            'DAX method'[Type],
            'DAX method'[Frequency]
        ),
        [Date] <= EARLIER ( [Date] )
    )
)

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[scott_hsd]

Thanks I'll give it a try and let you know

[Icey · Accepted solution]

Hi @scott_hsd ,

Hi I have an excel spreadsheet that only has the number of hours (either per month/quater or even once as a frequency) with a start date and end date per client. 

---

Do you mean that the hours is total value of each frequency?

For example, for Client 1, each month's hour is 15 and the total value is 15×5 = 75 and the expected result is as follow.

Client	Type	Hours	Frequency	Start	Expiry	ExpandDate	cumulative hours
Client 1	Contract	15	Monthly	1/07/2021	31/12/2021	1/07/2021	15
Client 1	Contract	15	Monthly	1/07/2021	31/12/2021	1/08/2021	30
Client 1	Contract	15	Monthly	1/07/2021	31/12/2021	1/09/2021	45
Client 1	Contract	15	Monthly	1/07/2021	31/12/2021	1/10/2021	60
Client 1	Contract	15	Monthly	1/07/2021	31/12/2021	1/11/2021	75
Client 1	Contract	15	Monthly	1/07/2021	31/12/2021	1/12/2021	90

For Client 15, total hours for one quarter is 60 and the expected result is as follow.

Client	Type	Hours	Frequency	Start	Expiry	ExpandDate	cumulative hours
Client 15	Contract	60	Quarterly	1/07/2021	30/06/2022	1/07/2021	60
Client 15	Contract	60	Quarterly	1/07/2021	30/06/2022	1/10/2021	120
Client 15	Contract	60	Quarterly	1/07/2021	30/06/2022	1/01/2022	180
Client 15	Contract	60	Quarterly	1/07/2021	30/06/2022	1/04/2022	240

Is this what you want? If I have any misunderstanding, please let me know.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[scott_hsd]

Thanks Icey, That worked well, I do have another question now. Now that I have this information I want to compare this to actual hours logged for the given date. The log table has the date and hours logged for that date. I have tried creating an auto calendar and linking this to the above table 'date' and the log date, but its not giving me want I want. I just want to see the logged hours for the given period, be it monthly/quarterly etc and add/subtract these from the running cumlative hours.

Thanks

Scott

[Icey]

Hi @scott_hsd ,

Have you tried to use RELATED function to get the corresponding hours for each date?

In addition, it's better to share some screenshots to show the issue you are meeting. Please cover sensitive information.

Best Regards,

Icey

[scott_hsd]

Hi The table below is what I am trying to achieve. I have a list of the contracted hours beit per month, quarterly etc, and I think what you have given me above is great and gives me soemthing to work with. So the above solution would be refelected in the below as column 1,2 and 3. 

In order to get column 4 I have another table which holds the hours worked (consumed) for a given date, I want to total these hours by month (column 3). Also columns 2,5 and 6 are calculated columns based on either the columns in the same row or previous row.

Let me know if you need this explainded further, thanks Scott

[scott_hsd]

Just another quick note, I have tried creating a Date table and linking it to both the contracted hours table (as per the DAX code above on the 'Date' column) and the hours logged date from the hours logged table which is simply a table with a date, hours logged and the user. I cannot get it to work.