Forum Discussion
Previous value measures not working as expected
Hi Experts
Based on the sample data in the PBIX...
I cannot get my previous value calculate column measure to work and return the expected result as shown in the image below
Measure
Current Row = RANKX( ALL('Table'), 'Table'[month], , ASC, Dense)Previous Row =
VAR CurrentRow = 'Table'[Current Row]
VAR PreviousRow = CALCULATE(
MAX('Table'[Current Row]),
ALL('Table'),
'Table'[family] = EARLIER('Table'[family]),
'Table'[DDate] < EARLIER('Table'[DDate])
)
RETURN IF( PreviousRow <> BLANK(), PreviousRow, CurrentRow )Previous Quantity =
CALCULATE(
MAX('Table'[qty]),
ALL('Table'),
'Table'[family] = EARLIER('Table'[family]),
'Table'[Current Row] = EARLIER('Table'[Previous Row])
) Anonymous , if you need a new column
previous Qty =
var _max = maxx(filter(Table, Table[family] = earlier([Family]) && [Date] < earlier([Date])), [Date])
return
maxx(filter(Table, Table[family] = earlier([Family]) && [Date] =_max ), [qty])For measure join date with the date table and try TI
examples
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145sPower BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKA
1 Reply
- amitchandak
Super User
Anonymous , if you need a new column
previous Qty =
var _max = maxx(filter(Table, Table[family] = earlier([Family]) && [Date] < earlier([Date])), [Date])
return
maxx(filter(Table, Table[family] = earlier([Family]) && [Date] =_max ), [qty])For measure join date with the date table and try TI
examples
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145sPower BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKA