calculate quantities at a moment in time

[cthurston]

Hello,
I am attempting to calculate Total Quotes at a moment in time.  I have an ID, close date and quote date columns.  What I wish to do in the 2nd table is to calculate how many opps had previously become quotes before the end of the month and had not closed before that month.  I also have a Close date Year filter on this report set to 2017.  I've tried several different filters on measures, to no success.

 Sample Table

[Icey · Accepted solution]

Hi @cthurston ,

I'm not quite sure what you want. If I understand you correctly, you can create a measure like so:

Measure =
CALCULATE (
    COUNTROWS ( 'Table' ) + 0,
    FILTER (
        'Table',
        'Table'[QualProspectToQuote Date] <> BLANK ()
            && MONTH ( 'Table'[QualProspectToQuote Date] ) <= MONTH ( MAX ( 'Date'[Date] ) )
            && MONTH ( 'Table'[Close Date Actual] ) > MONTH ( MAX ( 'Date'[Date] ) )
    )
)

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[cthurston]

Is Date[Date] related to your fact table in this case?  This looks like what I initially tried but maybe in my fustration I missed and operator.

[Icey]

Hi @cthurston ,

No relationship between the two tables.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.