Power Query Level of Detail

[jxkang]

I am trying to create a column which tells if an account is a new account in that week. 
I have an example data attached which is a daily log of an account. 

In this example, the account was created on 1/1/2023. 
When I try to look at it on weekly basis (using Week End Date), I want to flag the account as "new" for the week of 1/7/2023.

Is there a Power Query formula that I can achive this?

Date	Account Number	New Account Flag	Balance	Week End Date	Week End New Account Flag
1/1/2023	123456	Y	100
1/2/2023	123456	N	101
1/3/2023	123456	N	102
1/4/2023	123456	N	103
1/5/2023	123456	N	104
1/6/2023	123456	N	105
1/7/2023	123456	N	106	1/7/2023	Y
1/8/2023	123456	N	107
1/9/2023	123456	N	108
1/10/2023	123456	N	109
1/11/2023	123456	N	110
1/12/2023	123456	N	111
1/13/2023	123456	N	112
1/14/2023	123456	N	113	1/14/2023	N
1/15/2023	123456	N	114
1/16/2023	123456	N	115
1/17/2023	123456	N	116
1/18/2023	123456	N	117
1/19/2023	123456	N	118
1/20/2023	123456	N	119
1/21/2023	123456	N	120	1/21/2023	N
1/22/2023	123456	N	121
1/23/2023	123456	N	122
1/24/2023	123456	N	123
1/25/2023	123456	N	124
1/26/2023	123456	N	125
1/27/2023	123456	N	126
1/28/2023	123456	N	127	1/28/2023	N
1/29/2023	123456	N	128
1/30/2023	123456	N	129
1/31/2023	123456	N	130

[v-xuxinyi-msft]

Hi @jxkang

Did you get your problem solved? If not, please provide detailed sample data and your expected results. So that I can help you better. Show it as a screenshot or excel. Please remove any sensitive data in advance.

Best Regards,
Community Support Team _Yuliax

[spinfuzer]

Assuming the week end date is always at maximum +6 days from the first day of the week.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ddNLCoMwGEXhrRTHQnNvfG6ic5HufxutaUvlyhko0Rwd/B/Z90533V1cu76T6zBO78V2PJTyvt/a9eyPzpfu0TpFV6FzdAN0NboRuiG6Cboxuhm6Y3Xa3b75Avkcv12hW6JTgXDN8ArTQiWMQEYpI6BR0ghs1F6dth+/DwBJiSRQUioJmDRlCEBKIIGQUsggpBQyCLm0Mf23f2MySDmlDFJOKYOU8xQZhJxCBiGnkEHIKWQQ8vwZ03IZE0g5pSpIOaUqSNXTWXq+AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"Account Number" = _t, #"New Account Flag" = _t, Balance = _t, #"Week End Date" = _t, #"Week End New Account Flag" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Account Number", Int64.Type}, {"New Account Flag", type text}, {"Balance", Int64.Type}, {"Week End Date", type date}, {"Week End New Account Flag", type text}}),
    add_week_new_account_flag =
        let
            priorStep = #"Changed Type",
            new_date =  try Date.AddDays(priorStep[Date]{List.PositionOf(priorStep[New Account Flag], "Y")},6) otherwise null,
            add_new_column = 
                Table.AddColumn(
                    priorStep, 
                    "Week End New Acct Flag", 
                    each 
                        try
                            if [Week End Date] > new_date
                            then "N" 
                            else "Y"
                        otherwise null
                )
        in
            add_new_column
            
in
    add_week_new_account_flag

[Ashish_Mathur]

Hi,

Why do you want to do this in the Query Editor?  Why not as a calculated column formula using DAX?

[jxkang]

What would be the DAX formula for calculated column?

[Ashish_Mathur]

On second thoughts, i think a measure will be the best.  Sevenhills has already provided you with a measure.

[jxkang]

Is there a way to create a separate physical table (not table visual) with Weekly? 
A Measure wouldn't work for my case.

[Ashish_Mathur]

Hi,

Will this calculated column formula work?

Column = if(or(Data[Week End Date]=BLANK(),CALCULATE(COUNTROWS(Data),FILTER(Data,Data[Account Number]=EARLIER(Data[Account Number])&&Data[New Account Flag]="Y"&&Data[Date]>=EARLIER(Data[Date])-6&&Data[Date]<=EARLIER(Data[Date])))=BLANK()),BLANK(),"Y")

Hope this helps.

[sevenhills]

Try this Measure: (and not column)

Weekend New Account Flag = 

var _WK = SELECTEDVALUE('Table'[Week End Date])
var _data = filter(all('Table'), 'Table'[Date] >= _WK -7 && 'Table'[Date] <= _WK ) 

RETURN IF ( NOT ISBLANK(_WK), CALCULATE( max ( 'Table'[New Account Flag]), _data))

Change this column setting as 

Output: