Cumulative change by quarter using ProductX of measure?

whoopie_pie — Sun, 18 Sep 2022 21:41:59 GMT

Hello, I have a measure calculating the quarter on quarter unit price changes (call it Measure1). I now want to make another measure (Measure2) that multiplies (1+ values of Measure1) in all quarters up to the selected quarter. Sample data below:

Year-Quarter	Unit Price (Measure1)	Measure2 (output)
2022 Q1	-	1+0% = 100%
2022 Q2	30%	(100%)(1+30%*) = 130%
2022 Q3	15%	(100%)(130%)(1+15%) = 149.5%

Measure2 = // Variable for (1+Measure1) value VAR _n = IF(ISNUMBER('Calculations'[Unit Price]), 'Calculations'[Unit Price] + 1) RETURN CALCULATE(PRODUCTX( FILTER( ALLSELECTED('Date'[DecimalDate]), 'Date'[DecimalDate] <= SELECTEDVALUE('Date'[DecimalDate]) ), _n) )

My returns Measure2 output is ridiculously large numbers. Snapshot here. I cropped out the row names but each row represents a different supplier. My data starts at 2019 Q3 so Measure 2 should equal (Unit Price + 1) for that quarter. Everything after that should be a product building off of it. But you can see some funky numbers (eg row 2).

Re: Cumulative change by quarter using ProductX of measure?

lbendlin — Tue, 20 Sep 2022 00:09:00 GMT

You can't really put SELECTEDVALUE into a CALCULATE. Use variables instead.

Please provide sanitized sample data that fully covers your issue.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

topic Re: Cumulative change by quarter using ProductX of measure? in DAX Commands and Tips

Cumulative change by quarter using ProductX of measure?

Re: Cumulative change by quarter using ProductX of measure?