SUM of values within the next 90 Days

DAX DAX Calculations
1

Currently displaying the open 90 day pipeline total based on Est Closure date using a relative date filter on the page. This was fine in the beginning as the 90 day pipeline was independent of all other data so a page level filter was no problem. We’re expanding the data being pulled in and the comparisons we want to make. What I need to do now is house the date filtering within my measure to limit it to the next 90 days. I have a date table that spans from 1/1/2016 - 12/31/2018 linked on the Est Close. Current measure is as follows:

Test =
VAR MinDate =
    MIN ( Pipeline[Est Close] )
RETURN
    IF (
        MinDate < TODAY (),
        BLANK (),
        CALCULATE ( [Total Open Pipeline $], DATEADD ( Dates[Date], +90, DAY ) )
    )

This gives the correct results in a table, however, there is no total. Which means I cannot display the value on a card. What am I missing?

Thanks!

2

Just noticed posted this int he wrong sub, pls feel free to move

3

So this is showing the amount 90 day before it occurs with the +90, DAYS

Would I be able to see images of the results.

Also if you have a demo file I could quickly look at.

Thanks

4

Hi Sam,

Apologies for the delay in response,

Thats the goal, show the total value of the next 90 days, based on the Est Close Date. I got the total to show now, I had MIN and needed MAX, New measure as follows

Test new = 
VAR varDate = MAX ( Dates[Date] )
VAR varValue = CALCULATE( [Total Open Pipeline $], DATEADD( Dates[Date], +90, DAY ) )
RETURN
    IF (
        varDate < TODAY (),
        BLANK (),
        varValue )

This is better as my other was missing Aug 2018 data. Both are missing Nov data, which would apply as 90 days from today would bring us to November.

edna%20pic

Heres a sample file. I have a date table linked on Est Close Date. My Date Table spans 1/1/2016 - 12/31/2018 EDNA Sample.xlsx (19.8 KB)

Thanks!
-Randy

5

How about just trying something like this

Total Next 90 Days = 
SUMX( VALUES( Dates[Date] ),
       IF( AND( Dates[Date] >= TODAY(), Dates[Date] <= TODAY() + 90 ), [Sales LY], BLANK() ) )

Sub in the correct measure, mine is just a placeholder

Iterating through the date here is the important part

image
image.png793×496 19.5 KB

6

Thanks Sam,

I achieved the same with:

Total 90 Day Pipeline Test = CALCULATE(
    [Total Open Pipeline $], DATESBETWEEN( Pipeline[Est Close Date], TODAY(), TODAY()+90))
7

Ok that’s great.

8

Branching off of this, what if I am looking to see the same type of info based on next 3 months as opposed to 90 Days? Using the patterns we have here, I can go into Dec but don’t get the full month, which makes sense as it would be outside the 90 Day threshold. But what if we wanted to see through to the end of the last full month (in this case December)?

9

Try this one.

Sorry not super elegant but does the trick

Total Next 90 Days (Full Month) = 
VAR DaysWindow = VALUE( DATE( YEAR( TODAY() ), MONTH( TODAY() ) + 3, DAY( MAX( Dates[Date] ) ) ) - TODAY() )

RETURN
SUMX( VALUES( Dates[Date] ),
       IF( AND( Dates[Date] >= TODAY(), Dates[Date] <= TODAY() + DaysWindow ), [Sales LY], BLANK() ) )

image
image.png1038×736 40.1 KB

10

That works! Trying to understand the variable here, can you tell me if I am interpreting this correctly? Essentially returning a date based on todays date, pulling in the current year, the month +3, and the day by going to the MAX date in the month +3 definition. We are then taking that date and subtracting today to give the time between today and the desired end date.

Our measure is then just looking for date >= today and <= Today + Variable

11

Yep that’s the logic exactly.

I played around with a few ideas and this it the one I landed on which I thought worked the best.