Create custom fields that extracts values from a field

[Anonymous]

Hi,

I have a data like the one below and i want to create two custom field (Sales Yesterday and Sales last Week) that extract values from the Sold field for any row whose TxnDate is equal to the previous calendar date if it exist otherwise put blank. I also want this for last weeks sales.

Using today (07/17/2020) as an example,  the previous calendar date will be 07/16/2020 and last week sales date would be (07/05/ to 07/11/ 2020). Please note, i don't want to have to enter the dates myself  for previous day or last weeks date, i want them auto-generated. I am completely new to power query and no idea what DAX does please so be as explicit as possible. thanks so much for helping.

TxnDate	Prroduct	Sold
7/1/2020 : 7:37:44 PM	Beans	1
7/1/2020 : 7:45:29 AM	Beans	0
7/3/2020 : 7:37:44 PM	Rice	0
7/1/2020 : 5:37:44 AM	Fruit	3
7/16/2020 : 7:37:44 AM	Rice	0
7/5/2020 : 10:00:44 AM	Yam	4
7/16/2020 : 04:37:44 PM	Yam	5
7/16/2020 : 11:37:14 PM	Fruit	3
7/1/2020 : 5:37:13 AM	Yam	1
7/15/2020 : 7:37:44 PM	Fruit	0
7/4/2020 : 2:02:12 PM	Beans	1
7/5/2020 : 7:37:44 AM	Beans	0
7/16/2020 : 7:37:44 PM	Rice	2
7/10/2020 : 5:27:09 PM	Beans	0
7/16/2020 : 7:37:44 PM	Rice	0
7/16/2020 : 6:20:11AM	Rice	3
7/17/2020 : 7:37:44 PM	Rice	4
7/16/2020 : 12:37:44 PM	Fruit	8
7/16/2020 : 9:37:40 AM	Fruit	9

[v-alq-msft]

Hi, @Anonymous 

If you take the answer of someone, please mark it as the solution to help the other members who have same problems find it more quickly. If not, let me know and I'll try to help you further. Thanks.

Best Regards

Allan

[v-alq-msft · Accepted solution]

Hi, @Anonymous 

Based on your description, I created data to reproduce your scenario. I used 7/17/2020 as test date on my side. You need to modify it as Today(). The pbix file is attached in the end.

You may creatd measures as below.

Last week Sales = 
var tab = 
SUMMARIZE(
    'Table',
    'Table'[Prroduct],
    "Result",
    var _testdate = DATE(2020,7,17)
    var _product = [Prroduct]
    return
    CALCULATE(
        SUM('Table'[Sold]),
        FILTER(
            ALL('Table'),
            'Table'[Prroduct]=_product&&
            YEAR('Table'[TxnDate])=YEAR(_testdate)&&
            WEEKNUM('Table'[TxnDate])=WEEKNUM(_testdate)-1
        )
    )
)
var result =
SUMX(
    tab,
    [Result]
)
return
IF(
    ISBLANK(result),
    0,
    result
)

YesterDay Sales = 
var tab = 
SUMMARIZE(
    'Table',
    'Table'[Prroduct],
    "Result",
    var _testdate = DATE(2020,7,17)
    var _product = [Prroduct]
    return
    CALCULATE(
        SUM('Table'[Sold]),
        FILTER(
            ALL('Table'),
            'Table'[Prroduct]=_product&&
            'Table'[TxnDate]=_testdate-1
        )
    )
)
var result = 
SUMX(
    tab,
    [Result]
)
return
IF(
    ISBLANK(result),
    0,
    result
)

Result:

Best Regards

Allan

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

[Anonymous]

Hi @Anonymous ,

this is Power Query version:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZK7DsIwDEV/JcpcqbaTNMRbGdiQEBuqOlSoQwcYePw/pTQhbSLEEi9Hx1fXaRppSywJCAQLy8qy1uKwl4Xc9t31Pk6UbbGitGFyoo4pmCmVdR2Hcx9BQWVmaFLtbs/hMU7lqWrtqjMu4yEEBgjUqbuMr05UoONcH8okFOKbwplKgi3io4o3hrZMtgiv8um1p4iBGClffeLKVp/2FXdPnoJverIMbrnyP1lKVUzAiPGFQl32lyo9EVKusU3CuQmD5edxsm1f", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [xnDate = _t, Prroduct = _t, Sold = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"xnDate", type text}, {"Prroduct", type text}, {"Sold", Int64.Type}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type"," : "," ",Replacer.ReplaceText,{"xnDate"}),
    #"Parsed Date" = Table.TransformColumns(#"Replaced Value",{{"xnDate", each Date.From(DateTime.FromText(_, "en-US")), type date}}),
    
    #"Grouped Rows" = Table.Group(#"Parsed Date", {"xnDate", "Prroduct"}, {{"Sold", each List.Sum([Sold]), type number}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "WeekNum", each Date.WeekOfYear([xnDate]), Int64.Type),
    Yesterday = Table.TransformColumns(#"Added Custom",{{"xnDate", each _ + #duration(1,0,0,0), type datetime}, {"WeekNum", each _ + 1, type number}}),
    #"Merged Queries" = Table.NestedJoin(#"Added Custom", {"xnDate", "Prroduct"}, Yesterday, {"xnDate", "Prroduct"}, "Yesterday", JoinKind.LeftOuter),
    #"Expanded Yesterday" = Table.ExpandTableColumn(#"Merged Queries", "Yesterday", {"Sold"}, {"Yesterday"}),
    #"Merged Queries1" = Table.NestedJoin(#"Expanded Yesterday", {"WeekNum", "Prroduct"},  Table.Group(Yesterday, {"Prroduct", "WeekNum"}, {{"Sold", each List.Sum([Sold]), type number}}), {"WeekNum", "Prroduct"}, "Expanded Yesterday", JoinKind.LeftOuter),
    #"Expanded Expanded Yesterday" = Table.ExpandTableColumn(#"Merged Queries1", "Expanded Yesterday", {"Sold"}, {"Last Week"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Expanded Yesterday",{{"Prroduct", Order.Ascending}, {"xnDate", Order.Ascending}})

in
    #"Sorted Rows"

Kind regards,

JB

[lbendlin]

Do you have a Dates table? Is it marked as such? Is it wired into the data model? Does it have a week number column? Does it matter if "yesterday" is a weekend day?

[Anonymous]

@lbendlin 

I'm not sure what you mean by do i have a Dates table but i know there is a datetime field like i showed in the data i posted. Not sure what you mean by is it wired into the data model either, though i have other columns in the table so it's not  just those three fields.

I do not hava weekly number column and i do not mind if "yesterday" is a weekend but i want my week to start on a Sunday to Saturday. I hope this helps and please let me know if you need any clarification. Thanks. 

[lbendlin]

If this were my task then I would do all the things that I mentioned. Add a dates table into the data model, mark it as such, and add a week number column. After that it is trivial. without it - not so much.