Power Query - how we create a measure based on a dataset while union it back to the original dataset

[Peter_Price]

Hi Folks,

Wondering how we create a measure based on a dataset <Not only specific to date column> while union it back to the original dataset ?

May be it make it easier to view the problem visually, so i have created the picture below.

Link: Link

P.S. Somehow i have to do it in this way

thanks

[Peter_Price]

Hi Folks,

Wondering how we create a measure based on a dataset while union it back to the original dataset ?

May be it make it easier to view the problem visually, so i have created the picture below.

Link: Link

thanks

[Zubair_Muhammad]

HI @Peter_Price

Try this

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDU9UrM0zW0UNJRcgRiz7yUzLLMlNLEHCDHUClWB1WJE7oSIwwlzuhKjCFKjPBYZIKhBMMiUwwlGBaZKcXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Category = _t, #"Calc Type" = _t, Volume = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Category", type text}, {"Calc Type", type text}, {"Volume", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Category"}, {{"Date", each List.Max([Date]), type date}, {"All Rows", each _, type table}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Max Rows", each let mydate=[Date] in 
Table.SelectRows([All Rows],each [Date]= mydate)),
    #"Expanded Max Rows" = Table.ExpandTableColumn(#"Added Custom1", "Max Rows", {"Volume"}, {"Max Rows.Volume"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Max Rows", "Day Defore", each let mydate=[Date] in 
Table.SelectRows([All Rows],each [Date]= Date.AddDays(mydate,-1))),
    #"Expanded Day Defore" = Table.ExpandTableColumn(#"Added Custom", "Day Defore", {"Volume"}, {"Day Defore.Volume"}),
    #"Added Custom2" = Table.AddColumn(#"Expanded Day Defore", "Volume", each [Max Rows.Volume]-[Day Defore.Volume]),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom2",{"All Rows", "Max Rows.Volume", "Day Defore.Volume"}),
    #"Added Custom3" = Table.AddColumn(#"Removed Columns1", "Calc Type", each "Comparison"),
    #"Appended Query" = Table.Combine({#"Changed Type", #"Added Custom3"})
in
    #"Appended Query"

[Zubair_Muhammad]

@Peter_Price

File attached as well

You can follows the steps from Query Editor

[Zubair_Muhammad · Accepted solution]

@Peter_Price

And you can also use a DAX Calculated Table as well.

Assuming your Table Name is Table1.

From Modelling Tab >> New Table

File attached as well

Calculated Table =
VAR Rows_I_need =
    GENERATE (
        SELECTCOLUMNS ( VALUES ( Table1[Category] ), "Mycategory", [Category] ),
        CALCULATETABLE ( TOPN ( 1, Table1, [Date], DESC ) )
    )
VAR Add_Volumn_Difference =
    ADDCOLUMNS (
        Rows_I_need,
        "Difference", [Volume]
            - CALCULATE (
                SUM ( Table1[Volume] ),
                TOPN (
                    1,
                    FILTER (
                        Table1,
                        Table1[Category] = [Mycategory]
                            && Table1[Date] < EARLIER ( [Date] )
                    ),
                    [Date], DESC
                )
            )
    )
VAR ComparisonTable =
    SELECTCOLUMNS (
        Add_Volumn_Difference,
        "Date", [Date],
        "Category", [Category],
        "Calc Type", "Comparison",
        "Volumn", [Difference]
    )
RETURN
    UNION ( Table1, ComparisonTable )

[Ashish_Mathur]

Hi,

If the entries in the Date column are continuous, then try this

1. Create a Calendar Table with a relationship from the Date column of your source data Table to the Date column of your Calendar Table
2. Build a simple Table visual by dragging Date (from the Calendar Table) and Category columns
3. Write this measure = SUM(Data[Volume])-CALCULATE(SUM(Data[Volume]),PREVIOUSDAY(Calendar[Date]))

Hope this helps.

[Peter_Price]

Hi Ashish,

thanks for your reply and it is great.Sry that i didn't make myself clear.  Somehow i want to do

Create a measure based on existing dataset , it may involve sum(Cat B) - Sum(Cat C)  < Not only specific to date column>

then union that measure back to origninal dataset 

Thx