Calculate weekly hours from daily hours and week number - DAX

[e17aguilar]

Hello!

I need some help to calculate a new column using DAX. I've never done a calculation like this, so I'm lost. 

I have a weekly calendar, with the following columns: Week and Daily hour. Now I need a new column to count all the hours per week (  from 1 to 168 hours, 7 days * 24 hours = 168 hours ).

I wish to obtain the Weekly hour column for all the weeks of the year:

The 3 dots just represent the rest of the values between the shown values.  

I appreciate all your help!

[v-frfei-msft]

Hi @e17aguilar,

Here I made one sample for your reference.

1. Enter the sample data and create two calculated columns.

week = WEEKNUM(Table1[Date])

Column = IF(ISBLANK(Table1[time]),BLANK(),1)

2.Create a measure to achieve your goal.

Measure = CALCULATE(SUM(Table1[Column]),FILTER(ALLEXCEPT(Table1,Table1[week]),Table1[time]<=MAX(Table1[time])))

If you want to get the calculated column not the measure. Then please use the formula.

result = CALCULATE(SUM(Table1[Column]),Table1[time]<=EARLIER(Table1[time]))

For more details, please check the pbix as attached.

Regards,

Frank

[e17aguilar]

Hi Frank! @v-frfei-msft

Thank you for your help. 

I tried your "new column DAX formula" to calculate weekly hours, but it doesn't work for me, I don't know why, maybe because of the way I calcultate the calendar. 

Here is the pbix file, check it if you have a chance. 

Thanks in advance for your help. 

Kind regards, 

Ernesto

[v-frfei-msft · Accepted solution]

Hi @e17aguilar,

I made some update on your formulas. Seemed your missed time 8, so I added that by myself.

Calendar = 
ADDCOLUMNS (
    CROSSJOIN (
        CALENDAR (DATE(2016,1,2), DATE(2016,1,12)),
        UNION (
            ROW ( "Time", TIME ( 1, 0, 0 ) ),
            ROW ( "Time", TIME ( 2, 0, 0 ) ),
            ROW ( "Time", TIME ( 3, 0, 0 ) ),
            ROW ( "Time", TIME ( 4, 0, 0 ) ),
            ROW ( "Time", TIME ( 5, 0, 0 ) ),
            ROW ( "Time", TIME ( 6, 0, 0 ) ),
            ROW ( "Time", TIME ( 7, 0, 0 ) ),
            ROW ( "Time", TIME ( 8, 0, 0 ) ),
            ROW ( "Time", TIME ( 9, 0, 0 ) ),
            ROW ( "Time", TIME ( 10, 0, 0 ) ),
            ROW ( "Time", TIME ( 11, 0, 0 ) ),
            ROW ( "Time", TIME ( 12, 0, 0 ) ),
            ROW ( "Time", TIME ( 13, 0, 0 ) ),
            ROW ( "Time", TIME ( 14, 0, 0 ) ),
            ROW ( "Time", TIME ( 15, 0, 0 ) ),
            ROW ( "Time", TIME ( 16, 0, 0 ) ),
            ROW ( "Time", TIME ( 17, 0, 0 ) ),
            ROW ( "Time", TIME ( 18, 0, 0 ) ),
            ROW ( "Time", TIME ( 19, 0, 0 ) ),
            ROW ( "Time", TIME ( 20, 0, 0 ) ),
            ROW ( "Time", TIME ( 21, 0, 0 ) ),
            ROW ( "Time", TIME ( 22, 0, 0 ) ),
            ROW ( "Time", TIME ( 23, 0, 0 ) ),
            ROW ( "Time", TIME ( 24, 0, 0 ) )
        )
    ),
    "DateTime", [Date] + [Time]
)

Then we can create a calculated columns to work on that.

Weekly hour1 = CALCULATE(SUM(Calendar[Column]),FILTER(ALLEXCEPT('Calendar','Calendar'[Week]),'Calendar'[DateTime]<=EARLIER('Calendar'[DateTime])))

For more details, please check the pbix as attached.

Regards,

Frank

[e17aguilar]

Hello Frank!

Many thanks for your help. 

Unffurtanetly, the this code is not working for me, I receive this message after wait 5 minutes to calculate.

I think your code works, but it doesn't work for my model, maybe my model is too large. 

Finnaly, I decided to create a calendar using parameters and merging two queries. 

Many thanks for your time and your attention.

Regards,

Ernesto

[Greg_Deckler]

Are those all of the source columns that you have or do you have a Date column in there as well?

[e17aguilar]

Hi Greg,

I also have a Date and DateTime column: