Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Substract any row value from the previous row values

MonthQuarterData pointSales Quantity
Jan/1811                 262
Feb/1812                 732
Mar/1813                   15
Apr/1824                 958
May/1825                   22
Jun/1826                   49
Jul/1837                     7
Aug/1838                 907
Sep/1839                   19
Oct/18410                 115
Nov/18411              1.706
Dec/18412                 911
Jan/19113                   16
Feb/19114                 408
Mar/19115                   69
Apr/19216                     3
May/19217                 666
Jun/19218                   56
Jul/19319                     9
Aug/19320                 213
Sep/19321                   23
Oct/19422                   19
Nov/19423                   60
Dec/19424                   71
Jan/20125              1.045
Feb/20126                   12
Mar/20127                   91
Apr/20228              3.083
May/20229                   10
Jun/20230                 124
Jul/20331                     3
Aug/20332                 666
Sep/20333                   56
Oct/20434                     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:

 

Question.PNG

 

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

MonthData pointDifference 
Monday, 01 January 2018112 (13-1)
Thursday, 01 February 2018211 (13-2)
Thursday, 01 March 2018310 (13-3)
Tuesday, 01 January 201913  
Friday, 01 February 201914(14-13)
Friday, 01 March 201915(15-13)
Wednesday, 01 January 20202512 (25-13)
Saturday, 01 February 20202613 (26-13)
Sunday, 01 March 20202714 (27-13)

 

Anyone could please help with a DAX measure to calculate the difference between data points? Thank you very much in advance!

1 ACCEPTED SOLUTION
AlB
Super User
Super User

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 

SU18_powerbi_badge

 

 

View solution in original post

4 REPLIES 4
AlB
Super User
Super User

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 

SU18_powerbi_badge

 

 

Anonymous
Not applicable

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 

SU18_powerbi_badge

 

Anonymous
Not applicable

@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_.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors