Forum Discussion

Peter_Price's avatar
Peter_Price
Frequent Visitor
7 years ago
Solved

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 vi...
Need Help
Show and Tell
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    7 years ago

    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's avatar
Ashish_Mathur
Super User
7 years ago

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's avatar
Peter_Price
Frequent Visitor
7 years ago

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