Applying a monthly price measure on date-by-date granularity

[Veblengood]

I am attempting to create a average measure where the measure yields the avereage price from a dataset wtih a monthly granularity. The measure should be such that price remains constant within that month regardless of date granularity.  Months are on a 01.MM.YYYY basis kind of date format. My problem is that the price will be blank unless the date is e.g. 01.01.20 or 01.02.20.

I have two tables for reference: 
DimDates

FactPrice

FactPrice is connected with DimDates through Months by a many-to-many relantionship. DimDates has a daily granularity.

Price measure =

CALCULATE(AVERAGE(FactPrice[Price]), ALLEXCEPT(DimDates, DimDates[Month (01.MM.YYYY)]))

This yields an average price across the whole period. But again, I want to have the average price down to a daily granularity. Any ideas?

[Veblengood]

Any ideas? 🙂

[v-jingzhang]

Hi @Veblengood 

Sorry for the late response. You may try a measure like below. 

Price measure = 
VAR _monthStart = MAX(DimDates[Date]) - DAY(MAX(DimDates[Date])) + 1
RETURN
CALCULATE (
    MAX( FactPrice[Price] ),
    ALL(DimDates),
    DimDates[Date] = _monthStart
)

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[Veblengood]

Thanks for the reply, this is great!

This works well when looking at the development over time, however, if I wanted to look at the total average for a period, this measure retrieves the latest price. Is there an easy way I can use this measure give me the correct average numbers as well in the total?

[v-jingzhang]

Hi @Veblengood 

Do you have a MonthStartDate column in DimDates table? If so, you can try 

Price measure =
VAR _table =
    SUMMARIZE ( DimDates, DimDates[MonthStartDate] )
VAR _table2 =
    ADDCOLUMNS (
        _table,
        "avg_price",
            CALCULATE (
                MAX ( FactPrice[Price] ),
                ALL ( DimDates ),
                DimDates[Date] = [MonthStartDate]
            )
    )
RETURN
    AVERAGEX ( _table2, [avg_price] )

[Veblengood]

Hi thanks,

I do have MonthStartDate Column in both the Dimdates and FactPrice. However, I can't quite get code to work. I still end up with the latest price in the total. Since I am going to multiply the price with a volume, this becomes wrong when looking over a longer period than a month. I have attached an example file of my data. Do you know how I can solve this?

https://www.dropbox.com/s/we0am3b1ls4x8zn/Price%20Measure.pbix?dl=0

[Veblengood]

@v-jingzhang 

[v-jingzhang]

Hi @Veblengood 

If I understand it correctly, the average price for a month is the price value of the month start date, right? If so, you can try below measure

Price measure =
CALCULATE (
    MAX ( FactPrice[Price] ),
    ALLEXCEPT ( DimDates, DimDates[Month (01.MM.YYYY)] )
)

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[Veblengood]

Thanks for the reply!

Unfortunately, this yields a constant price on a lower granularity, ie. date and week.

What I attempting is for the price to be constant until it is updated every month: