Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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:

 

 

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 2019141 (14-13)
Friday, 01 March 2019152 (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!

Need Help
  • AlB's avatar
    AlB
    5 years ago

    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 

     

     

4 Replies

  • AlB's avatar
    AlB
    Community Champion
    5 years ago

    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 

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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? 

      • AlB's avatar
        AlB
        Community Champion
        5 years ago

        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