Forum Discussion

eblake's avatar
eblake
Frequent Visitor
10 years ago
Solved

Calculated Measure of values in same column

I have a table that I add a new snapshot of data to monthly. The date the new data is entered is the Snapshot Date column (Screenshot - Forecasted Data Table attached). I would like to calculate the difference in the column 'Recognized + Projected Revenue (CAD)' between Snapshots. For Example: I would like the Difference in 'Recognized + Projected Revenue (CAD)' in 'Snapshot Date' "May 2016" and 'Snapshot Date' "June 2016" for project = "X" at 'Date' = "July 2016".

 

I have also attached a picture of how I have configured my Matrix that is displaying the data.

 

Any help would be very appreciated.

 

 

                  

  • v-haibl-msft's avatar
    v-haibl-msft
    10 years ago

    eblake

     

    You can do it with several slicers and tables as below. We only need to create a column and measure with following DAX formula.

    I’ve also upload my .pbix file here for reference.

    Date YearMonth = 
FORMAT ( 'Forecasted Data'[Date], "mmmm yyyy" )
    Diff = 
CALCULATE (
    SUM ( 'Forecasted Data'[Recognized + Projected Revenue (CAD)] ),
    FILTER (
        'Forecasted Data',
        'Forecasted Data'[Snapshot Date] = MAX ( 'Forecasted Data'[Snapshot Date] )
    )
)
    - CALCULATE (
        SUM ( 'Forecasted Data'[Recognized + Projected Revenue (CAD)] ),
        FILTER (
            'Forecasted Data',
            'Forecasted Data'[Snapshot Date] = MIN ( 'Forecasted Data'[Snapshot Date] )
        )
)

     

    Best Regards,

    Herbert

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    10 years ago

    Can you provide some sample data and information on expected output that we could use to engineer a solution?

    • eblake's avatar
      eblake
      Frequent Visitor
      10 years ago

      Hello,

      I have attached a screenshot of a sample ot put table, I have a matrix formatted like this table, just missing the differnce column. I have also pasted in a sample of the table I am using for the raw data.

      Snapshot DateDateCompanyProjectProject ClientProject Project stageProject Billable ProjectProject Customer Contract TypeProject First booking dateResources - Booked hours with actualsRecognized + Projected Revenue (CAD)Projected cost [primary loaded cost] (CAD)Actual + Projected Margin (CAD)
      5/23/20165/1/2016Test CompanyProject AClient AVAActiveBillableFirm Fixed Price12/1/20161032650000
      5/23/20166/1/2016Test CompanyProject AClient AVAActiveBillableFirm Fixed Price12/1/20161123000000
      5/23/20167/1/2016Test CompanyProject AClient AVAActiveBillableFirm Fixed Price12/1/2016953300000
      5/23/20168/1/2016Test CompanyProject AClient AVAActiveBillableFirm Fixed Price12/1/20161301250000
      5/23/20169/1/2016Test CompanyProject AClient AVAActiveBillableFirm Fixed Price12/1/20161062500000
      5/23/201610/1/2016Test CompanyProject AClient AVAActiveBillableFirm Fixed Price12/1/20161253500000
      5/23/201611/1/2016Test CompanyProject AClient AVAActiveBillableFirm Fixed Price12/1/20161025500000
      5/23/201612/1/2016Test CompanyProject AClient AVAActiveBillableFirm Fixed Price12/1/20161092340000
      5/23/20161/1/2017Test CompanyProject AClient AVAActiveBillableFirm Fixed Price12/1/20160000
      5/23/20162/1/2017Test CompanyProject AClient AVAActiveBillableFirm Fixed Price12/1/20160000
      5/23/20163/1/2017Test CompanyProject AClient AVAActiveBillableFirm Fixed Price12/1/20160000
      5/23/20164/1/2017Test CompanyProject AClient AVAActiveBillableFirm Fixed Price12/1/20160000
      5/23/20165/1/2017Test CompanyProject AClient AVAActiveBillableFirm Fixed Price12/1/20160000
      5/23/20166/1/2017Test CompanyProject AClient AVAActiveBillableFirm Fixed Price12/1/20160000
      5/23/20167/1/2017Test CompanyProject AClient AVAActiveBillableFirm Fixed Price12/1/20160000
      5/23/20168/1/2017Test CompanyProject AClient AVAActiveBillableFirm Fixed Price12/1/20160000
      5/23/20169/1/2017Test CompanyProject AClient AVAActiveBillableFirm Fixed Price12/1/20160000
      5/23/201610/1/2017Test CompanyProject AClient AVAActiveBillableFirm Fixed Price12/1/20160000
      5/23/201611/1/2017Test CompanyProject AClient AVAActiveBillableFirm Fixed Price12/1/20160000
      5/23/201612/1/2017Test CompanyProject AClient AVAActiveBillableFirm Fixed Price12/1/20160000
      5/23/20161/1/2018Test CompanyProject AClient AVAActiveBillableFirm Fixed Price12/1/20160000
      5/23/20162/1/2018Test CompanyProject AClient AVAActiveBillableFirm Fixed Price12/1/20160000
      5/23/20163/1/2018Test CompanyProject AClient AVAActiveBillableFirm Fixed Price12/1/20160000
      5/23/20164/1/2018Test CompanyProject AClient AVAActiveBillableFirm Fixed Price12/1/20160000
      5/23/20165/1/2018Test CompanyProject AClient AVAActiveBillableFirm Fixed Price12/1/20160000
      6/23/20165/1/2016Test CompanyProject AClient AVAActiveBillableFirm Fixed Price12/1/20161002500000
      6/23/20166/1/2016Test CompanyProject AClient AVAActiveBillableFirm Fixed Price12/1/20161022550000
      6/23/20167/1/2016Test CompanyProject AClient AVAActiveBillableFirm Fixed Price12/1/20161052450000
      6/23/20168/1/2016Test CompanyProject AClient AVAActiveBillableFirm Fixed Price12/1/20161072340000
      6/23/20169/1/2016Test CompanyProject AClient AVAActiveBillableFirm Fixed Price12/1/20161004500000
      6/23/201610/1/2016Test CompanyProject AClient AVAActiveBillableFirm Fixed Price12/1/20161025500000
      6/23/201611/1/2016Test CompanyProject AClient AVAActiveBillableFirm Fixed Price12/1/20161056500000
      6/23/201612/1/2016Test CompanyProject AClient AVAActiveBillableFirm Fixed Price12/1/20161072340000
      6/23/20161/1/2017Test CompanyProject AClient AVAActiveBillableFirm Fixed Price12/1/20160000
      6/23/20162/1/2017Test CompanyProject AClient AVAActiveBillableFirm Fixed Price12/1/20160000
      6/23/20163/1/2017Test CompanyProject AClient AVAActiveBillableFirm Fixed Price12/1/20160000
      6/23/20164/1/2017Test CompanyProject AClient AVAActiveBillableFirm Fixed Price12/1/20160000
      6/23/20165/1/2017Test CompanyProject AClient AVAActiveBillableFirm Fixed Price12/1/20160000
      6/23/20166/1/2017Test CompanyProject AClient AVAActiveBillableFirm Fixed Price12/1/20160000
      6/23/20167/1/2017Test CompanyProject AClient AVAActiveBillableFirm Fixed Price12/1/20160000
      6/23/20168/1/2017Test CompanyProject AClient AVAActiveBillableFirm Fixed Price12/1/20160000
      6/23/20169/1/2017Test CompanyProject AClient AVAActiveBillableFirm Fixed Price12/1/20160000
      6/23/201610/1/2017Test CompanyProject AClient AVAActiveBillableFirm Fixed Price12/1/20160000
      6/23/201611/1/2017Test CompanyProject AClient AVAActiveBillableFirm Fixed Price12/1/20160000
      6/23/201612/1/2017Test CompanyProject AClient AVAActiveBillableFirm Fixed Price12/1/20160000
      6/23/20161/1/2018Test CompanyProject AClient AVAActiveBillableFirm Fixed Price12/1/20160000
      6/23/20162/1/2018Test CompanyProject AClient AVAActiveBillableFirm Fixed Price12/1/20160000
      6/23/20163/1/2018Test CompanyProject AClient AVAActiveBillableFirm Fixed Price12/1/20160000
      6/23/20164/1/2018Test CompanyProject AClient AVAActiveBillableFirm Fixed Price12/1/20160000
      6/23/20165/1/2018Test CompanyProject AClient AVAActiveBillableFirm Fixed Price12/1/20160000
      7/23/20165/1/2016Test CompanyProject AClient AVAActiveBillableFirm Fixed Price12/1/20161072600000
      7/23/20166/1/2016Test CompanyProject AClient AVAActiveBillableFirm Fixed Price12/1/20161102450000
      7/23/20167/1/2016Test CompanyProject AClient AVAActiveBillableFirm Fixed Price12/1/2016982300000
      7/23/20168/1/2016Test CompanyProject AClient AVAActiveBillableFirm Fixed Price12/1/2016100500000
      7/23/20169/1/2016Test CompanyProject AClient AVAActiveBillableFirm Fixed Price12/1/20161022500000
      7/23/201610/1/2016Test CompanyProject AClient AVAActiveBillableFirm Fixed Price12/1/20161023500000
      7/23/201611/1/2016Test CompanyProject AClient AVAActiveBillableFirm Fixed Price12/1/20161056000000
      7/23/201612/1/2016Test CompanyProject AClient AVAActiveBillableFirm Fixed Price12/1/20161072340000
      7/23/20161/1/2017Test CompanyProject AClient AVAActiveBillableFirm Fixed Price12/1/20160000
      7/23/20162/1/2017Test CompanyProject AClient AVAActiveBillableFirm Fixed Price12/1/20160000
      7/23/20163/1/2017Test CompanyProject AClient AVAActiveBillableFirm Fixed Price12/1/20160000
      7/23/20164/1/2017Test CompanyProject AClient AVAActiveBillableFirm Fixed Price12/1/20160000
      7/23/20165/1/2017Test CompanyProject AClient AVAActiveBillableFirm Fixed Price12/1/20160000
      7/23/20166/1/2017Test CompanyProject AClient AVAActiveBillableFirm Fixed Price12/1/20160000
      7/23/20167/1/2017Test CompanyProject AClient AVAActiveBillableFirm Fixed Price12/1/20160000
      7/23/20168/1/2017Test CompanyProject AClient AVAActiveBillableFirm Fixed Price12/1/20160000
      7/23/20169/1/2017Test CompanyProject AClient AVAActiveBillableFirm Fixed Price12/1/20160000
      7/23/201610/1/2017Test CompanyProject AClient AVAActiveBillableFirm Fixed Price12/1/20160000
      7/23/201611/1/2017Test CompanyProject AClient AVAActiveBillableFirm Fixed Price12/1/20160000
      7/23/201612/1/2017Test CompanyProject AClient AVAActiveBillableFirm Fixed Price12/1/20160000
      7/23/20161/1/2018Test CompanyProject AClient AVAActiveBillableFirm Fixed Price12/1/20160000
      7/23/20162/1/2018Test CompanyProject AClient AVAActiveBillableFirm Fixed Price12/1/20160000
      7/23/20163/1/2018Test CompanyProject AClient AVAActiveBillableFirm Fixed Price12/1/20160000
      7/23/20164/1/2018Test CompanyProject AClient AVAActiveBillableFirm Fixed Price12/1/20160000
      7/23/20165/1/2018Test CompanyProject AClient AVAActiveBillableFirm Fixed Price12/1/20160000
  • v-haibl-msft's avatar
    v-haibl-msft
    Microsoft Employee
    10 years ago

    eblake

     

    You can do it with several slicers and tables as below. We only need to create a column and measure with following DAX formula.

    I’ve also upload my .pbix file here for reference.

    Date YearMonth = 
FORMAT ( 'Forecasted Data'[Date], "mmmm yyyy" )
    Diff = 
CALCULATE (
    SUM ( 'Forecasted Data'[Recognized + Projected Revenue (CAD)] ),
    FILTER (
        'Forecasted Data',
        'Forecasted Data'[Snapshot Date] = MAX ( 'Forecasted Data'[Snapshot Date] )
    )
)
    - CALCULATE (
        SUM ( 'Forecasted Data'[Recognized + Projected Revenue (CAD)] ),
        FILTER (
            'Forecasted Data',
            'Forecasted Data'[Snapshot Date] = MIN ( 'Forecasted Data'[Snapshot Date] )
        )
)

     

    Best Regards,

    Herbert