Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
Peter_Price
Frequent Visitor

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

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

1 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 )

CTabl.png

View solution in original post

6 REPLIES 6
Peter_Price
Frequent Visitor

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

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"

 

 

@Peter_Price

 

File attached as well

 

You can follows the steps from Query Editor

 

PQSolution.png

@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 )

CTabl.png

Ashish_Mathur
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.