Get the price update factor

[Aquila]

Hello, I need to get a table of factors from a table of prices per month and per year. Below I show the Price table and what to get:

You must get the division of each month and year for the same month but last year. For example the price of the month of January/2015 should be divided by January/2018, January/2016 by January/2018. I need to get a price update factor per month and year. I leave the file in Excel with the formulas used.
Thanks a lot.

 

[GraH - Guido]

Aquila,

Do you mean like this ?
- [K3]= Tabla1[@2015]/Tabla1[@[2018]:[2018]]
- Drag down and across.

The red part is a mixed structured reference: relative by row (@ used) and absolute by column ([2018]:[2018]).

EDIT: sorry for the earlier goof-up. Morning coffee just kicked-in. You'd need to fetch 2018 automatically, so when 2019 arrives, the calculation uses the column 2019?

 

[GraH - Guido]

Perhaps this then? [K3]=Tabla1[@2015]/INDEX(Tabla1,,COUNTA(Tabla1[#Headers]))

 

[Aquila]

That's right, your solution is very good, but I've omitted to say I wanted to do it with Dax formulas. I've tried some for I haven't been able to.

 

[Chihiro]

You don't need DAX formula for this.

Just load the table flattened in PQ into data model.

Create pivot table. Add Months to row label (apply manual sorting the first time).
Then add years into column label. Sum of Price into values field.

Then right click on value field. "Show Value As"->% Of->2018.

See attached.

 

[Aquila]

Thank you very much, very ingenious! This is just the solution that I have to get to, but for the exercise, I have passed only a summary, to focus directly on the problem I have. Now I tell you everything, it happens that I did not want to complicate.
I'm working on Power BI. I need to get a table with the price update factors of a sales table, but only the products "A ", of an ABC classification. To do this classification I have been asked to do so taking into account the amount of sales per product. OK, this has been simple, I have created columns calculated in the Product table, and I have obtained the list of products "A ". From here, the problem arises. It is necessary to create a table with the list of products "A ", valued at cost price per year and per month; This could do with the following Dax formula.



This is the table that I have passed, and from which I must arrive at the solution that you have shown me so well. I must add a column with the factors, in the style of Excel "Show Value As"->% Of->2018
Thanks a lot.

 

[GraH - Guido]

That's right, your solution is very good, but I've omitted to say I wanted to do it with Dax formulas. I've tried some for I haven't been able to.

Make sense why you posted it in this forum...

Normally a construction using PARALLELPERIOD would work.
something like PARALLELPERIOD(<dates>,-1,year)
Have a read of https://docs.microsoft.com/en-us/dax/parallelperiod-function-dax

 

[Chihiro]

I wouldn't do transformation in DAX. Unless there's other related table (dimension table) to original format and you don't want to alter it. Even if you do... you can reference original query and make transformation. This avoids issues associated with calculated tables in DAX.

Just do transformation like done in PQ (Query Editor in PowerBI), but add number in front of month name.
Ex: 01 - jan, 02 - feb.

This part can be done in DAX or M. I did it in DAX using following Caluclated Column.

SortMonth = FORMAT(MONTH(Datevalue([month]&" "& "1," & [Year])),"00") & " - " & [month]

Now create measure to calculate monthly total for 2018 regardless of filter context, using ALLEXCEPT.

2018Val = CALCULATE(SUM(Table1[Value]),FILTER(ALLEXCEPT(Table1,Table1[SortMonth]),[Year]=2018))

Next, is to add factor calculation.

%Of2018 = SUM(Table1[Value])/[2018Val]

Then format the measure as %.

Add matrix visual, set Rows to [SortMonth], Columns to [Year] and Values [%Of2018]. Remove Column subtotals from the visual.



FYI - Parallel period will not work without date level grain data with date dimension table.

See attached.

 

[Aquila]

Very good Chihiro!!! I've been able to fix it. Thanks a lot.