Table Transformation with DAX

I have a Table (Table1) with date, product and price columns. I calculated Monthly change as Table2 using a measure (Monthly%_Change) and created a new column called "Change". I used the Table2 ...

Whitewater100
4 years ago
Hi Vikrant:

Off the top of my head I can't see a way to do that, (I'm sure someone knows). Generally, the when the data model is set up with fact and dim tables it allows for easier DAX and more complex analysis at the same time. Staying in one table can only take you so far.

Sorry if this doesn't do the table answer you'd like.

Here is one measure that produces the same results:(with the Date Table in Model)
MOM % Avg Price 2 =
var monthavg = AVERAGEX(VALUES(Dates[Month]),
average(TableA[Price])
)
var PrevMontPrice = CALCULATE([Monthly Avg], PREVIOUSMONTH(Dates[Date]))
var currprice = SELECTEDVALUE(TableA[Price])
var pricediff = monthavg - PrevMontPrice
var monumber = SELECTEDVALUE(Dates[Month No.])
return
IF( NOT(ISBLANK(currprice) && monumber >=2),
DIVIDE(pricediff, [Prev Mont Price]))

The Generate Table function might help here, not entirely sure...

tamerj1

4 years ago

Hi VikrantC

You may try

Table3 =
ADDCOLUMNS (
    SUMMARIZE ( Table1, Calender[End Of Month] ),
    "Product A%", CALCULATE ( [Monthly%_Change], Table1[Product] = "Product A" ),
    "Product B%", CALCULATE ( [Monthly%_Change], Table1[Product] = "Product B" )
)

tamerj1
4 years ago
Great VikrantC
If it solved your problem, would you please consider marking my reply as accepted solution?

Forum Discussion

Table Transformation with DAX