Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
Progress to date:
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
Hi there.
Do you still need this?
Best
Darek
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
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.
-- 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
UPDATE:
I got #2 by using a running total idea
yes...
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
24 | |
20 | |
15 | |
13 |
User | Count |
---|---|
51 | |
46 | |
39 | |
19 | |
19 |