cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Raulx_7
Helper I
Helper I

Inventory price update

Hi, I have 3 tables:

- Dates 

- Products 

- Price 

 

The current price is calculated using a metric, where can be specified if the value is the current or accumulated. 

This measure is called: CPrice, and the value that is using to calculate is Products[productValue]

 

My goal is to find the change in price form each product, since the filter is set by year, I want to check the product in order to compare the selected month and the previous one. 

 

For example: 

Filter in the visual selected: Year:  2020, Month July.

FruitJanFebMarchAprilMayJunJulyWhat I want
Apple     57+2
Orange     31-2

 

I want to know the it the item is lower or higher compared to the previous month. 

 

Reading other post with similar things in common I decided to crate a measure with two variables

 

 

 

PriceChange = 
VAR LastPirce =
    CALCULATE (
        VALUES (Price[Price]  ),
        FILTER ( Dates, Dates[Date] = MAX ( Dates[Date] ) )
    )
VAR SecondLastDate =
    CALCULATE (
        MAX ( Dates[Date] ),
        FILTER ( Dates, Dates[Date] < MAX ( Dates[Date] ) )
    )
RETURN
    LastPirce
        - CALCULATE ( VALUES ( Price[Price] ), Dates[Date] = SecondLastDate )

 

However, I did not get any result in when I use the variable in my matrix. 

I'm trying to visualize it in a matrix where the fields using are: 

 

ROWS: 2 columns that specify category and name of the product. 

Columns: Date[Year-Month] [Month]

Values: CPrice, Price, PriceChange

The result of PriceChange is empty.

1 ACCEPTED SOLUTION
RicoZhou
Community Support
Community Support

Hi @Raulx_7 

According to your statement, I know your have three tables.

- Dates 

- Products 

- Price 

From your code, I think Price table should be related with Dates table, but I don't know what your data model actually looks like.

I think your connection mode is live connection, so you couldn't build a rank column.

I update the measure.

NewMeasure = 
VAR _CPrice =
    SUM ( 'Price'[Price] )
VAR _LastMonthEnd =
    EOMONTH ( MAX ( 'Date'[Date] ), -1 )
VAR _LastMonthStart =
    EOMONTH ( MAX ( 'Date'[Date] ), -2 ) + 1
VAR _LMPrice =
    CALCULATE (
        SUM ( 'Price'[Price] ),
        FILTER (
            ALL ( 'Date' ),
            'Date'[Date] >= _LastMonthStart
                && 'Date'[Date] <= _LastMonthEnd
        )
    )
RETURN
    _CPrice - _LMPrice

Result is as below.

1.png

Best Regards,

Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

 

 

View solution in original post

3 REPLIES 3
RicoZhou
Community Support
Community Support

Hi @Raulx_7 

I build a sample to have a test, I think you need to use all function in filter field in your dax code or you will get empty result.

My Sample is as below.

Price Table:

1.png

Product Table:

2.png

Date Table:

Date =
VAR _T =
    ADDCOLUMNS (
        CALENDARAUTO (),
        "Year", YEAR ( [Date] ),
        "Month", MONTH ( [Date] ),
        "MonthName", FORMAT ( [Date], "MMM" ),
        "YearMonth",
            YEAR ( [Date] ) * 100
                + MONTH ( [Date] )
    )
VAR _ADDRANK =
    ADDCOLUMNS ( _T, "RANK", RANKX ( _T, [YearMonth],, ASC, DENSE ) )
RETURN
    _ADDRANK

Relationship:

3.png

Measure:

PriceChange =
VAR _CPrice =
    SUM ( 'Price'[Price] )
VAR _LastMonth =
    MAX ( 'Date'[RANK] ) - 1
VAR _LMPrice =
    CALCULATE (
        SUM ( 'Price'[Price] ),
        FILTER ( ALL ( 'Date' ), 'Date'[RANK] = _LastMonth )
    )
RETURN
    _CPrice - _LMPrice

Build a rank for each yearmonth in date, then you just need to get price in current rank-1(price in last month.). Result is as below.

4.png

Select 2020,July.

5.png

Best Regards,

Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

 

Thanks, but my problem is that I cannot edit the tables and relations in this dashboard (is connected to a cube), I can only create measures. Then create the rank column is not possible. 

RicoZhou
Community Support
Community Support

Hi @Raulx_7 

According to your statement, I know your have three tables.

- Dates 

- Products 

- Price 

From your code, I think Price table should be related with Dates table, but I don't know what your data model actually looks like.

I think your connection mode is live connection, so you couldn't build a rank column.

I update the measure.

NewMeasure = 
VAR _CPrice =
    SUM ( 'Price'[Price] )
VAR _LastMonthEnd =
    EOMONTH ( MAX ( 'Date'[Date] ), -1 )
VAR _LastMonthStart =
    EOMONTH ( MAX ( 'Date'[Date] ), -2 ) + 1
VAR _LMPrice =
    CALCULATE (
        SUM ( 'Price'[Price] ),
        FILTER (
            ALL ( 'Date' ),
            'Date'[Date] >= _LastMonthStart
                && 'Date'[Date] <= _LastMonthEnd
        )
    )
RETURN
    _CPrice - _LMPrice

Result is as below.

1.png

Best Regards,

Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

 

 

View solution in original post

Helpful resources

Announcements
Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Microsoft Ignite 768x460.png

Find your focus

Explore the latest tools,training sessions,technical expertise, networking and more.

Power BI Womens Summit 2021 768 x460.jpg

Interviews, learning sessions, allies, and more!

#PowerBIWomenSummit

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!