Values for just the remaining Quarter or Year

[Anonymous]

Hello Forum -

I have a measure that works perfectly (without having to add a visual filter) to give me the remaining amount of forecast for the current month.  In other words, we have forecast dollars from our CRM system and of course these forecasts go out well into next year.     The measure below returns the value of those forcasts for just the remaining days of the current month.  

What I need is the same type of measure, for the Quarter, and for the Year.   But I can't get ENDOFQUARTER OR ENDOFYEAR to work, and of course I need them to work without having to add a filter to the visual.   Any help is appreciated!

ROM Forecast = CALCULATE( SUM('AllOpps-Products-Sean'[Extended Price]) , 'AllOpps-Products-Sean'[Est.Close] >= today(), 'AllOpps-Products-Sean'[Est.Close] <= EOMONTH(TODAY(),0))

[jdbuchanan71 · Accepted solution]

@Anonymous 

This measure has both end of quarter and end of year as variables and you just change which one you use in the final calculate.

Measure = 
VAR _Today = TODAY()
VAR _MonthsToEOQ = ROUNDUP( DIVIDE ( MONTH ( _Today ), 3 ), 0 ) * 3 - MONTH ( _Today )
VAR _EOM = EOMONTH ( _Today, 0 )
VAR _EOQ = EOMONTH ( _Today, _MonthsToEOQ )
VAR _EOY = DATE ( YEAR ( _Today ), 12, 31 )
RETURN
CALCULATE (
    SUM ( 'AllOpps-Products-Sean'[Extended Price] ),
    ALL ( 'AllOpps-Products-Sean' ),
    'AllOpps-Products-Sean'[Est.Close] >= _Today,
    'AllOpps-Products-Sean'[Est.Close] <= _EOY
)

Right now it is calculating to end of year, for the end of quarter measure it would be the same except for the last line would be:

    'AllOpps-Products-Sean'[Est.Close] <= _EOQ

[Anonymous · Accepted solution]

I found the issue. Your formula is correct.  

It includes "today" and for some reason the weekly binning that I set up using the "bin" feature in Power Bi was excluding today's date, the 28th.    Perhaps because it is a weekend?  

In any case, it was good to work through that so I dicovered the issue with binning my weeks.  

Marked as solved - thanks again!

[jdbuchanan71 · Accepted solution]

@Anonymous 

This measure has both end of quarter and end of year as variables and you just change which one you use in the final calculate.

Measure = 
VAR _Today = TODAY()
VAR _MonthsToEOQ = ROUNDUP( DIVIDE ( MONTH ( _Today ), 3 ), 0 ) * 3 - MONTH ( _Today )
VAR _EOM = EOMONTH ( _Today, 0 )
VAR _EOQ = EOMONTH ( _Today, _MonthsToEOQ )
VAR _EOY = DATE ( YEAR ( _Today ), 12, 31 )
RETURN
CALCULATE (
    SUM ( 'AllOpps-Products-Sean'[Extended Price] ),
    ALL ( 'AllOpps-Products-Sean' ),
    'AllOpps-Products-Sean'[Est.Close] >= _Today,
    'AllOpps-Products-Sean'[Est.Close] <= _EOY
)

Right now it is calculating to end of year, for the end of quarter measure it would be the same except for the last line would be:

    'AllOpps-Products-Sean'[Est.Close] <= _EOQ

[Anonymous]

To the rescue again JD, thank you. 

Ok, so your calculation is coming up with something slightly different than another calculation I am using that seems to be providing acccurate results just comparing it to raw table values.   Here is what I am using for the "remaining month" caculation, which in theory given we are at the end of the quarter, should return the same result if trying to find the remaining value for the end of this quarter.    My end of month caculation returns $11,102,271.   I am "binning" the weeks and this includes all of the days left in this quarter so the number in my table I believe is correct.    Your end of quarter calculation results in $11,406,471.   Not sure why there is a difference. 

Could it be 

Remaining Opportunity for Month = CALCULATE(

SUM('AllOpps-Products-Sean'[Extended Price])

,FILTER(

Dates

,Dates[Date] = ENDOFMONTH('AllOpps-Products-Sean'[Est.Close])))

[Anonymous · Accepted solution]

I found the issue. Your formula is correct.  

It includes "today" and for some reason the weekly binning that I set up using the "bin" feature in Power Bi was excluding today's date, the 28th.    Perhaps because it is a weekend?  

In any case, it was good to work through that so I dicovered the issue with binning my weeks.  

Marked as solved - thanks again!

[Anonymous]

@jdb  @jdbuchanan71  - I was also using this formula for the rest of the year calculation, and it also works.   

ROY Forecast = CALCULATE( SUM('AllOpps-Products-Sean'[Extended Price]) , 'AllOpps-Products-Sean'[Est.Close] >= today(), 'AllOpps-Products-Sean'[Est.Close] <= DATE(YEAR(TODAY()),12,31))

One thing, is that this formula is filterable - it I put a visual filter in the page, it filters  (in my case, by 50% vs 80% probability of closure for example).   Using CRM sales data.    ROY = Rest of Year

I noticed that I cannot filter your formula and I am wondering why?  Can you help me understand that?   Is there a way to make it filterable?  

[jdbuchanan71 · Accepted solution]

It is the ALL in mine, try it without:

Measure = 
VAR _Today = TODAY()
VAR _MonthsToEOQ = ROUNDUP( DIVIDE ( MONTH ( _Today ), 3 ), 0 ) * 3 - MONTH ( _Today )
VAR _EOM = EOMONTH ( _Today, 0 )
VAR _EOQ = EOMONTH ( _Today, _MonthsToEOQ )
VAR _EOY = DATE ( YEAR ( _Today ), 12, 31 )
RETURN
CALCULATE (
    SUM ( 'AllOpps-Products-Sean'[Extended Price] ),
    'AllOpps-Products-Sean'[Est.Close] >= _Today,
    'AllOpps-Products-Sean'[Est.Close] <= _EOY
)

I put the ALL in there in case you were looking at it in a month context

[Anonymous]

Ok, got it.  I just added // in front of the All statement and yes, works with a filter in place perfectly.  

Thank you!