Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Need Help Using Slicer to Calculate Difference Between Two Dates

Hi Everyone -

 

I'm looking for help to use a slicer to dynamically calculate the difference between the two dates selected. This is for my "office market statistics project." Specifically, I'm looking to easily show the difference in absorption once a user chooses two dates.

 

Absorption is calculated by the difference in occupied square feet over a given period - it can be positive or negative. Occupied SF is calculated by subtracting the vacant SF from the "rentable building area (RBA)."

 

So for the situation below, the dates are 7/1/2014 - 4/1/2015, and in card visual below, it would display "3,486,142."

 

Occupied SF in 4/1/2015 = 136,426,764

Occupied SF in 7/1/2014 = 132,940,621

Absorption = 3,486,142

 

 

These numbers/stats will be updated each month and will all be in the same table. Any help would be greatly appreciated!

Thanks,

Paul

 

Need Help
Tips and Tricks
Tutorial Requests

2 Replies

  • OwenAuger's avatar
    OwenAuger
    Super User
    9 years ago

    Hi Paul Anonymous

     

    I'm assuming you have a date table called 'Date' with date column 'Date'[Date], otherwise rename.

     

    Also you've already defined an [Occ SF] measure by the looks of it.

     

    Then a measure something like this should do the trick:

     

    Absorption Between First and Last Date =
CALCULATE ( [Occ SF], FIRSTDATE ( 'Date'[Date] ) )
    - CALCULATE ( [Occ SF], LASTDATE ( 'Date'[Date] ) )

     

    Owen :)

  • v-sihou-msft's avatar
    v-sihou-msft
    Microsoft Employee
    9 years ago

    Anonymous

     

    OwenAuger's solution is correct. Just use LASTDATE() calculation minus the FIRSTDATE() calculation.

     

     

    Regards,