Calculating Price Indices over a Date Range

[tcboutte]

I am struggling to figure out the best combination of measures to help a Power Bi visual match a source Excel file. The source file contains a price for each month of the year which are represented below and the Total column is what I am struggling with. "DaysSelected" is a measure that is there for my reference and reacts to my date slicer.

The correct value for the Total column is 94.38 and is to be calculated as the sum of: Price per month *(days in that month/DaysSelected). For this example that would be 82.98*(31/90) + 91.63*(28/90) + 108.26*(31/90) = 94.38. 

The measure in the matrix above is NYMEX-CL = CALCULATE(AVERAGE('Price Indices'[Amount]),'Price Indices'[Category] = "NYMEX-CL") and below is the input data and DaysInMonth is a calculated column that I added. Any suggestions?

[v-yanjiang-msft · Accepted solution]

Hi @tcboutte ,

According to your description, Here's my solution. Create a measure.

NYMEX-CL =
VAR _Av =
    CALCULATE (
        AVERAGE ( 'Price Indices'[Amount] ),
        'Price Indices'[Category] = "NYMEX-CL"
    )
RETURN
    IF (
        ISINSCOPE ( 'Price Indices'[Date] ),
        _Av,
        SUMX (
            FILTER ( 'Price Indices', 'Price Indices'[Category] = "NYMEX-CL" ),
            'Price Indices'[Amount]
                * DIVIDE ( 'Price Indices'[DaysInMonth], [DaysSelected] )
        )
    )

Get the correct result.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

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

[tcboutte]

Much appreciated! I just need to make one small change to how I was calculating my DaysSelected, but your measure did the trick after that. Thanks!

[v-yanjiang-msft]

Hi @tcboutte ,

You're welcome! I thought DaysSelected was a column you've built, I see it in your screenshot。

If not, just create a new table use GENERATESERIES function like this:

Best Regards,
Community Support Team _ kalyj

[tcboutte]

All good! DaysSelected was actually a measure that calculated how many were currently selected on that page's slicer since we have about 2 years of data to work from.

[v-yanjiang-msft]

Hi @tcboutte ,

If I understand correctly, you want to count the numbers be selected in the slicer, simply try:

DaysSelected = COUNTROWS(ALLSELECTED('Table'[Column]))

Get the correct value.

Best Regards,
Community Support Team _ kalyj

[amitchandak]

@tcboutte , Try a measure like

Rolling 3 = calculate(AverageX(Values('Date'[MONTH Year]),calculate([Measurement]* day(eomonth(max('Date'[date]),0))/90)),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-3,MONTH))
)

[tcboutte]

Thanks. I am working with this now, but it looks like it be built to only handle the total days being 90. I'll need it to work with any number of months selected from my date slicer so that the denominator is dynamic. Do you think that is possible?