DAX for Price development (3, 6, 12 Months ago)

[y5famfnatudu]

Dear All, your assistance is so much appreciated
I have a Power BI Matrix that displays dates, products, and measures calculating the total amount, total purchase prices, and Unit Price (calculated as purchase price divided by total amount).

I want to determine the Unit Price for the products from 3, 6, and 12 months ago, both in terms of value and percentage change. These values should represent the average Unit Price for the entire calendar month.
Power BI dataset/report attached

Simon

[v-yangliu-msft · Accepted solution]

Thanks for the reply from Brightsider , please allow me to provide another insight: 
Hi  @y5famfnatudu ,

You may consider the following measure:

3 months ago =
IF(
    [Unit Price]=BLANK(),BLANK(),
AVERAGEX(
    FILTER(ALLSELECTED('Date'),
    'Date'[Date]>EOMONTH(MAX('Date'[Date]),-4)&&'Date'[Date]<=EOMONTH(MAX('Date'[Date]),-1)),[Unit Price])
)

6 months ago =
IF(
    [Unit Price]=BLANK(),BLANK(),
AVERAGEX(
    FILTER(ALLSELECTED('Date'),
    'Date'[Date]>EOMONTH(MAX('Date'[Date]),-7)&&'Date'[Date]<=EOMONTH(MAX('Date'[Date]),-1)),[Unit Price])
)

12 months ago =
IF(
    [Unit Price]=BLANK(),BLANK(),
AVERAGEX(
    FILTER(ALLSELECTED('Date'),
    'Date'[Date]>EOMONTH(MAX('Date'[Date]),-13)&&'Date'[Date]<=EOMONTH(MAX('Date'[Date]),-1)),[Unit Price])
)

Best Regards,

Liu Yang

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

[v-yangliu-msft · Accepted solution]

Thanks for the reply from Brightsider , please allow me to provide another insight: 
Hi  @y5famfnatudu ,

You may consider the following measure:

3 months ago =
IF(
    [Unit Price]=BLANK(),BLANK(),
AVERAGEX(
    FILTER(ALLSELECTED('Date'),
    'Date'[Date]>EOMONTH(MAX('Date'[Date]),-4)&&'Date'[Date]<=EOMONTH(MAX('Date'[Date]),-1)),[Unit Price])
)

6 months ago =
IF(
    [Unit Price]=BLANK(),BLANK(),
AVERAGEX(
    FILTER(ALLSELECTED('Date'),
    'Date'[Date]>EOMONTH(MAX('Date'[Date]),-7)&&'Date'[Date]<=EOMONTH(MAX('Date'[Date]),-1)),[Unit Price])
)

12 months ago =
IF(
    [Unit Price]=BLANK(),BLANK(),
AVERAGEX(
    FILTER(ALLSELECTED('Date'),
    'Date'[Date]>EOMONTH(MAX('Date'[Date]),-13)&&'Date'[Date]<=EOMONTH(MAX('Date'[Date]),-1)),[Unit Price])
)

Best Regards,

Liu Yang

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

[y5famfnatudu]

Dear @v-yangliu-msft,

Thank you so much for your assistance, I really appreciate it.

I'm just wondering where are the values for the 3 month back in your measure are coming from, because for example:

- If we look at march, 3 month back means December, your measure however is showing 0.85, which can't be true as there is no december

- I added more dates and purchases to the dataset, the values still don't make sense.

I really would appreciate it so much if you can help me here. I'm attaching a newer version of the file

Price development 

Best regards,

Simon

[y5famfnatudu · Accepted solution]

Dear @v-yangliu-msft ,

I think I figured where the issue was, it's in the (-1) you had in the measure, because it was calculating the average for the whole previous period, but I wanted it to calculate it just for the 1 month that was 3 month ago.
I have done minor changes to your measure:

3 months ago = 
VAR __NumberOfMonths = 3
VAR __LastDate4MonthsAgo = EOMONTH ( MAX ( 'Date'[Date] ), -1 * __NumberOfMonths - 1 )
VAR __LastDate3MonthsAgo = EOMONTH ( MAX ( 'Date'[Date] ), -1 * __NumberOfMonths )
RETURN
IF (
    [Unit Price] = BLANK (),
    BLANK (),
    AVERAGEX (
        FILTER (
            ALLSELECTED ( 'Date' ),
            'Date'[Date] > __LastDate4MonthsAgo
                && 'Date'[Date] <= __LastDate3MonthsAgo
        ),
        [Unit Price]
    )
)

Thank you once again @v-yangliu-msft 

Best regards,

Simon

[Brightsider]

I would recommend adding Offset columns to your Date table, and have those columns be recalculated every time the table is refreshed. That way, you could have a CurrentMonthOffset column whose values would tell you how far off from the current month your date is (0 for the current month, -1 for last month, +1 for the next month, etc).

An example of how to set something like this up is given in this thread.

Then, to do the calculation, your measure would look something like this:

UnitPrice6MonthAvg = CALCULATE(AVERAGE([UnitPrice]), FILTER(Date[CurrentMonthOffset] >= -5 && Date[CurrentMonthOffset <= 0))

[y5famfnatudu]

Dear @Brightsider,

Thank you so much for your kind response.

However, I'm not allowed to add columns to the data, I'm just allowed to create measures to the PBI file.

Best regards,

Hayman