multiple measure on table and 2 date columns

[Mmahachi]

I have a table with an ID and 2 date columns, the date due and the actual completed date. These dates can be anyday and I need to count the ids for each quarter based on the following definitions, 

The calculations are:

1. Items Due In Q = those with ‘Final Expected Date’ in the Quarter whether open or completed
2. Carryover from prior Q’s still open = prior to the Quarter end date and still open
3. Closed in Quarter = those completed (with 'actual completed date')within the quarter (unless already counted in #1)

Progress to date:

• # 1 I have that’s done
• #2 I cannot get anything to show up, this is my latest dax measure
• Carryover = CALCULATE(DISTINCTCOUNT(TABLE[IDFld]), TABLE[Open]="Yes", FILTER(TABLE, [Final Expected Date]<STARTOFQUARTER(TABLE[Final Expected Date])))
• #3 is showing a random number I can’t even figure out where it’s coming from
• The expected number for each category for Q1(are fiscal Q1 is oct-dec) are #1 38, #2 136 and #3 7

I have a relationship with the date table and the 'final expected date'(due date) and an inactive relationship on the Actual completed date and the date table since the actual completed date is only needed for #3. pbix file im working with is herePBIX

[Anonymous]

Hi there.

Do you still need this?

Best

Darek

[Mmahachi]

ok so I may have been too excited just seeing anything come through on the #2 calculation needed it seems that is is only taking the date filter and not the other filter I need applied with is the item is still 'Open' This is my latest attempt but it isn't taking the Table[Open]="Yes" part of the filter 

Carryover = var priordates = STARTOFQUARTER(Dates[Date])

RETURN

CALCULATE(COUNT(Table[IDFld]), Table[Open]="Yes",
FILTER( ALLEXCEPT((Table), Table[Open]), Table[Final Expected Date]<priordates))

[Mmahachi]

really really could use some help 

I have to show this in a chart, just has the quarters on the axis but the measure I want to create need to remove that filter context, so in Q1 for example I need to calculate a measure that looks at rows with dates prior to Q1 AND in a status of 'open'. I have tried a million variations on FILTER, ALL, ALLEXCEPT but it won't show any values when I try to place both the date and open filters.

I'm thinking that when I release the date filter with allexcept, it doesn't know where to go for the variable. How can I remove the date context but still use a variable to reference what context was there originally. 

Carryover = var priordates = STARTOFQUARTER(Dates[Date])

RETURN

CALCULATE(DISTINCTCOUNT(OCIA[IDFld]),

ALLEXCEPT(OCIA, OCIA[Open]), OCIA[Final Expected Date], FILTER(

OCIA, AND (OCIA[Open]="Yes", OCIA[Final Expected Date]<priordates)

)

[Anonymous]

-- First of all, you have to have a proper Date
-- table that's connected in a one-to-many
-- manner to you fact table through the
-- Completed Date field. In the Date table
-- you should store all your time units, like
-- fiscal your, fiscal month, fiscal week...
-- This table - depending on your requirements -
-- should also be connected to the other field,
-- Due Date. One of the relationships must be
-- inactive. Say, it's the one with Completed Date.

-- The below will work not only for quarters but
-- for any period you choose from your Date table.

[# Items] = DISTINCTCOUNT ( T[ID] )

-- This gives you the number of items where
-- the Due Date falls in the period of time
-- you've selected from your Date table.
-- No heed is taken of whether the items are
-- completed or not (as required).
[Items Due] = [# Items]

[Items Closed] =
	CALCULATE (
		[# Items],
		USERELATIONSHIP ( T[Completed Date], Dates[Date] )
	)

 [Carryover From Prev Quarter] =
    CALCULATE(
        [Items Due],
        PREVIOUSQUARTER( Dates[Date] ),
        -- I'm not sure whether you should
        -- use the below or try to us the
        -- Completed Date... but you're not
        -- clear about what the difference is
        -- between Open = "yes" and the Completed
        -- Date.
        T[Open] = "yes"
    )
    	
-- Bear in mind that
-- PREVIOUSQUARTER returns a table that contains a column
-- of all dates from the previous quarter,
-- based on the FIRST DATE in the date column as seen
-- in the current context. So, if you, for instance, select,
-- a whole your from your Dates table, then PREVIOUSQUARTER
-- will return the last quarter of the previous year. If you
-- need any other logic with regard to this, you'll have to
-- code it.
	
[Closed in Quarter] = [Items Closed]
	
-- Bear in mind that [Closed in Quarter] will also include
-- the items that were due in the current period and have
-- already been closed.

Best

Darek

[Mmahachi]

UPDATE:

I got #2 by using a running total idea

Carryover = var priordates = STARTOFQUARTER(Dates[Date])

RETURN

CALCULATE(DISTINCTCOUNT(OCIA[IDFld]),

FILTER(ALL(TABLE), TABLE[Final Expected Date]<priordates)

,OCIA[Open]="Yes")

and #3 by bringing in a separate date table, so now i have one for due dates and one for completed dates.

Hope this helps someone else!

[Mmahachi]

yes...