topic Re: Calculating Price Indices over a Date Range in DAX Commands and Tips

Calculating Price Indices over a Date Range

tcboutte — Mon, 01 Aug 2022 16:13:59 GMT

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?

Re: Calculating Price Indices over a Date Range

amitchandak — Mon, 01 Aug 2022 17:06:10 GMT

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))
)

Re: Calculating Price Indices over a Date Range

tcboutte — Tue, 02 Aug 2022 13:12:03 GMT

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?

Re: Calculating Price Indices over a Date Range

v-yanjiang-msft — Thu, 04 Aug 2022 07:14:33 GMT

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.

Re: Calculating Price Indices over a Date Range

tcboutte — Thu, 04 Aug 2022 14:29:59 GMT

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!

Re: Calculating Price Indices over a Date Range

v-yanjiang-msft — Fri, 05 Aug 2022 05:35:38 GMT

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

Re: Calculating Price Indices over a Date Range

tcboutte — Fri, 05 Aug 2022 13:10:50 GMT

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.

Re: Calculating Price Indices over a Date Range

v-yanjiang-msft — Mon, 08 Aug 2022 01:40:33 GMT

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