Forum Discussion
Substract any row value from the previous row values
Hi Anonymous
You need to create an additional one-column table to use as slicer to select the base date. Otherwise, using the date column in your fact table would filter the values in the visual down to only the selected date.
1. Create that additional table with the date values from the date table:
BaseDatesTable = DISTINCT(Table1[Month])or with others if you need a more complete/standard list. No relationships with the fact table
2. Create this measure and place it in the visual:
Difference = VAR currentDataPoint_ = SELECTEDVALUE ( Table1[Data point] ) VAR currentDate_ = SELECTEDVALUE ( Table1[Month] ) VAR baseDate_ = SELECTEDVALUE ( BaseDatesTable[Month] ) VAR baseDataPoint_ = CALCULATE ( DISTINCT ( Table1[Data point] ), Table1[Month] = baseDate_, ALL ( Table1 ) ) RETURN baseDataPoint_ - currentDataPoint_3. See it all at work in the attached file
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Hi AlB ,
It works for the mentioned case, however, when I applied the same calculation to another case, then I get an error:
A table of multiple values was supplied where a single value was expected. Could you please help?
Anonymous
That means, most likely, that there are dates in your data that appear more than once and with different DataPoint values. That would cause this bit to throw the error:
VAR baseDataPoint_ =
CALCULATE (
DISTINCT ( Table1[Data point] ),
Table1[Month] = baseDate_,
ALL ( Table1 )
)You'd need to decide what to do in those cases and update the code accordingly
Can you check if there are repeated dates? Or perhaps paste here the data that causes the error.
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
AlB tried pasting the data here from excel but there was an error about invalid HTML. What you explained is totally right. I just made all the same dates that repeat in the sales data have the data point, but it did not work. The mentioned case also have repeated dates and still works well, so I am wondering if there is another way to modify the VAR baseDataPoint_.