Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Month | Quarter | Data point | Sales Quantity |
Jan/18 | 1 | 1 | 262 |
Feb/18 | 1 | 2 | 732 |
Mar/18 | 1 | 3 | 15 |
Apr/18 | 2 | 4 | 958 |
May/18 | 2 | 5 | 22 |
Jun/18 | 2 | 6 | 49 |
Jul/18 | 3 | 7 | 7 |
Aug/18 | 3 | 8 | 907 |
Sep/18 | 3 | 9 | 19 |
Oct/18 | 4 | 10 | 115 |
Nov/18 | 4 | 11 | 1.706 |
Dec/18 | 4 | 12 | 911 |
Jan/19 | 1 | 13 | 16 |
Feb/19 | 1 | 14 | 408 |
Mar/19 | 1 | 15 | 69 |
Apr/19 | 2 | 16 | 3 |
May/19 | 2 | 17 | 666 |
Jun/19 | 2 | 18 | 56 |
Jul/19 | 3 | 19 | 9 |
Aug/19 | 3 | 20 | 213 |
Sep/19 | 3 | 21 | 23 |
Oct/19 | 4 | 22 | 19 |
Nov/19 | 4 | 23 | 60 |
Dec/19 | 4 | 24 | 71 |
Jan/20 | 1 | 25 | 1.045 |
Feb/20 | 1 | 26 | 12 |
Mar/20 | 1 | 27 | 91 |
Apr/20 | 2 | 28 | 3.083 |
May/20 | 2 | 29 | 10 |
Jun/20 | 2 | 30 | 124 |
Jul/20 | 3 | 31 | 3 |
Aug/20 | 3 | 32 | 666 |
Sep/20 | 3 | 33 | 56 |
Oct/20 | 4 | 34 | 9 |
Above is the data that I have about sales quantity per month from January 2018 to October 2020. I want to look into the sales quantity by Quarter of the year as shown below:
What I have tried to do is to find the diffrence between the data points, for example, if I take Thursday, 01 Janurary 2019 with data point 13 as a base, then the result I want to have looks like as below: 3 columns Month, Data point and Difference
Month | Data point | Difference | |
Monday, 01 January 2018 | 1 | 12 | (13-1) |
Thursday, 01 February 2018 | 2 | 11 | (13-2) |
Thursday, 01 March 2018 | 3 | 10 | (13-3) |
Tuesday, 01 January 2019 | 13 | ||
Friday, 01 February 2019 | 14 | 1 | (14-13) |
Friday, 01 March 2019 | 15 | 2 | (15-13) |
Wednesday, 01 January 2020 | 25 | 12 | (25-13) |
Saturday, 01 February 2020 | 26 | 13 | (26-13) |
Sunday, 01 March 2020 | 27 | 14 | (27-13) |
Anyone could please help with a DAX measure to calculate the difference between data points? Thank you very much in advance!
Solved! Go to Solution.
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 @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_.