Forum Discussion

Raulx_7's avatar
Raulx_7
Helper I
5 years ago
Solved

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: ...
Need Help
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    5 years ago

    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.

    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. 

     

     

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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:

Product Table:

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:

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.

Select 2020,July.

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. 

 

  • Raulx_7's avatar
    Raulx_7
    Helper I
    5 years ago

    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. 

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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.

      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.