cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Mmahachi Regular Visitor
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

 

stacked measures.PNG

6 REPLIES 6
Super User
Super User

Re: multiple measure on table and 2 date columns

Hi there.

 

Do you still need this?

 

Best

Darek

Mmahachi Regular Visitor
Regular Visitor

Re: multiple measure on table and 2 date columns

yes...

Mmahachi Regular Visitor
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!
Mmahachi Regular Visitor
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))
Highlighted
Mmahachi Regular Visitor
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. ociachart.PNG

 

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

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Top Kudoed Authors
Users Online
Currently online: 56 members 1,013 guests
Please welcome our newest community members: