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.
Hi,
I would like to get a measure that is returning the number of days that a contract is current in a given month.
I have a table with dates (It's a unique list with continuous dates, Week day, moth, year, etc...)
table with sales (Contains: Accounts, Dates, Product, Revenue, etc...)
table with Accounts (Unique list of Accounts
table with contracts (Unique list of Contracts, Account, Start Date, End Date)
The relationships are modelled as follows
When I try the following I get the week days for the whole range dim_Date[Date]
Working days= Calculate(COUNT(dim_Date[Date]), FILTER('dim_Date',AND('dim_Date'[DayOfWeekNumber]<>6,'dim_Date'[DayOfWeekNumber]<>7)), FILTER(ALL(dim_Date),dim_Date[Date]<=calculate(MAX(dim_Date[Date]))) )
When I try the following I get the number of days they order as per the Sales table which at least identify the start and finish date. My problem is that it is counting dates in the sales table instead of counting in the date table.
Count Contracts Days= CALCULATE ( COUNTA( dim_Date[Date] ), FILTER ( FILTER(dim_Date,dim_Date[Date] >= CALCULATE ( MAX ( 'Contracts'[Start Date]))), AND(dim_Date[DayOfWeekNumber] <> 6,dim_Date[DayOfWeekNumber] <> 7 )), FILTER (dim_Date,dim_Date[Date] <= CALCULATE (MAX('Contract'[End Date]))) )
Solved! Go to Solution.
Hi,
as far as I can see the problem is that you try to use 2 different date columns on the datetable without having 2 different relationships.
My idea would be to try it with mathematic
1. Count all rows
2. Count all rows <= Start date
3. Count all rows >= End date
4. 1. - 2. - 3. = result
Try to use variables. Should looks like this.
CCD = var allrows = COUNTROWS(dim_Date[Date]) var exstart = CALCULATE(COUNTROWS(dim_Date[Date]),FILTER(dim_Date, dim_Date(Date)<=MAX(Start_date)) var exend = CALCULATE(COUNTROWS(dim_Date[Date]),FILTER(dim_Date, dim_Date(Date)>=MAX(End_date);USERELATIONSHIP(dim_Date(date),end_date)) return allrows - exstart - exend
Important is that you define in calculate a new relationship between the second date column and the datekey as mentioned in the beginning of my post.
I'm not sure if it works but this would be my way. Good luck
Hi,
as far as I can see the problem is that you try to use 2 different date columns on the datetable without having 2 different relationships.
My idea would be to try it with mathematic
1. Count all rows
2. Count all rows <= Start date
3. Count all rows >= End date
4. 1. - 2. - 3. = result
Try to use variables. Should looks like this.
CCD = var allrows = COUNTROWS(dim_Date[Date]) var exstart = CALCULATE(COUNTROWS(dim_Date[Date]),FILTER(dim_Date, dim_Date(Date)<=MAX(Start_date)) var exend = CALCULATE(COUNTROWS(dim_Date[Date]),FILTER(dim_Date, dim_Date(Date)>=MAX(End_date);USERELATIONSHIP(dim_Date(date),end_date)) return allrows - exstart - exend
Important is that you define in calculate a new relationship between the second date column and the datekey as mentioned in the beginning of my post.
I'm not sure if it works but this would be my way. Good luck
Thanks Spuder, it's an interesting approach that works! I haven't started to deploy variables and it looks very practical.
I spent some more time looking at the relationships and by making them single I got the desired result.
Thanks
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 |
---|---|
97 | |
94 | |
74 | |
71 | |
64 |
User | Count |
---|---|
143 | |
109 | |
103 | |
82 | |
74 |