Creating weekly measures/calc on a daily grain fact table

[wildmight2017]

Good afternoon,

I'm trying to understand what's the best way to calculate measure on a weekly level from day grain

Question A: i'd like to receive a measure with value of 3 for employee 1  (instead of selecting Week column from time dim)

Question B (slightly more difficult): if daily flag >= 1 then 1 else 0.  so I'd like to see 1 for 1st employee and 0 for 2nd. 

Is there a way to accomplish this without additional data modeling using only DAX expressions? 

Here's the data set in a fact table:

employee_id	date	flag
1	20160101	1
1	20160102	0
1	20160103	1
1	20160104	0
1	20160105	0
1	20160106	0
1	20160107	1
2	20160101	0
2	20160102	0
2	20160103	0
2	20160104	0
2	20160105	0
2	20160106	0
2	20160107	0

Thank you

[v-shex-msft · Accepted solution]

HI @wildmight2017,

>>Question A: i'd like to receive a measure with value of 3 for employee 1  (instead of selecting Week column from time dim)

Does the 'value of 3' which mentioned above means the wednesday or 3th date?

If means 'wednesday', you can add a calculate column to store the 'day of week', then filter records based on this column.
Sample:

Day of Week = 
VAR year =
    LEFT ( [date], 4 )
VAR month =
    LEFT ( RIGHT ( [date], 4 ), 2 )
VAR day =
    RIGHT ( RIGHT ( [date], 4 ), 2 )
RETURN
    WEEKDAY ( DATE ( year, month, day ), 2 )

Wednesday Records = CALCULATETABLE(employee,employee[Day of Week]=3) 

If means 3th date, you can try to add a index column, then filter on it.

Index = COUNTROWS(FILTER(ALL(employee),[employee_id]=EARLIER(employee[employee_id])&&[date]<=EARLIER([date])))

3th Records = CALCULATETABLE(employee,employee[Index]=3) 

>>Question B (slightly more difficult): if daily flag >= 1 then 1 else 0.  so I'd like to see 1 for 1st employee and 0 for 2nd. 

You can refer to below formulas which about summary table with flag and get the rank.

Result =
VAR temp =
    ADDCOLUMNS ( employee, "Result", IF ( [flag] >= 1, 1, 0 ) )//add column to convert flag based on your condition.
VAR summary =
    SUMMARIZE (
        temp,
        [employee_id],
        "Count", SUMX ( FILTER ( temp, [employee_id] = EARLIER ( [employee_id] ) ), [Result] )//summary table with id and result.
    )
RETURN
    ADDCOLUMNS ( summary, "Rank", RANKX ( summary, [Count],, DESC ) )// add rank column

 

Regards,

Xiaoxin Sheng