Power Query Level of Detail

Question

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

sevenhills · Answer

Try this Measure: (and not column)
&nbsp;
&nbsp;
Weekend New Account Flag =

var _WK = SELECTEDVALUE('Table'[Week End Date])
var _data = filter(all('Table'), 'Table'[Date] &gt;= _WK -7 &amp;&amp; 'Table'[Date] &lt;= _WK )

RETURN IF ( NOT ISBLANK(_WK), CALCULATE( max ( 'Table'[New Account Flag]), _data))
&nbsp;
&nbsp;
Change this column setting as&nbsp;

&nbsp;
Output:&nbsp;

&nbsp;

ashish_mathur · Answer

Hi,
Why do you want to do this in the Query Editor?&nbsp; Why not as a calculated column formula using DAX?

spinfuzer · Answer

Assuming the week end date is always at maximum +6 days from the first day of the week.&nbsp;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] &gt; new_date
                            then "N" 
                            else "Y"
                        otherwise null
                )
        in
            add_new_column
            
in
    add_week_new_account_flag