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 ...
  • 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

v-haibl-msft's avatar
v-haibl-msft
Icon for Microsoft Employee rankMicrosoft 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