cancel
Showing results for
Did you mean:
Regular Visitor

## multiple measure on table and 2 date columns

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

6 REPLIES 6
Super User

## Re: multiple measure on table and 2 date columns

Hi there.

Do you still need this?

Best

Darek

Regular Visitor

yes...

Highlighted
Regular Visitor

## Re: multiple measure on table and 2 date columns

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!
Regular Visitor

## Re: multiple measure on table and 2 date columns

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))
Regular Visitor

## Re: multiple measure on table and 2 date columns

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)

)
Super User

## Re: multiple measure on table and 2 date columns

```-- 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,
-- 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

Best

Darek

Announcements

#### Community News & Announcements

Get your latest community news and announcements.

#### Power Platform Summit North America

Register by September 5 to save \$200

#### Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 412 members 4,346 guests
Recent signins: