Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Create custom fields that extracts values from a field

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 ...
Need Help
  • v-alq-msft's avatar
    v-alq-msft
    6 years ago

    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.

v-alq-msft's avatar
v-alq-msft
Community Support
6 years ago

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's avatar
    Anonymous
    Not applicable
    6 years ago

    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