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 there, novice user and first time poster here, hopefully I've posted this in the correct way.
I'm trying to produce a column in a table that provides a sum of one column in my table based on two conditions.
I've already created my date table in the accepted format, which looks generally like:
Date | Year | Month |
28/08/2020 | 2020 | Aug |
29/08/2020 | 2020 | Aug |
30/08/2020 | 2020 | Aug |
Continues...... |
I also have another table, fed from a SQL query, the relevant columns to my query look like....
Start Date | Duration (wks) | Selection Count |
17/08/2020 | 3 | 2 |
24/08/2020 | 8 | 5 |
31/08/2020 | 5 | 1 |
I want to add a claculated column to my date table that does a sumifs on my Selection count if the date row satisfies the condition of being within the date range created by the combination of Start Date and Duration.
In MS Excel, this would look something along the lines of =sumifs('Selection Count', 'Date' >= 'StartDate', 'Date' <= ('Start Date' + 7*'Duration'))
Hopefully that makes sense and someone can point me in the right direction.
Any and all help is appreciated, happy to provide more infomration/clarity if needed.
Thanks in advance.
Ed
Solved! Go to Solution.
@ejoneslor , Try a new column in date table
sumx(filter(table, date[date] >=table[startdate] && date[date] <=table[startdate] +7 ),table[selection count])
@ejoneslor , Try a new column in date table
sumx(filter(table, date[date] >=table[startdate] && date[date] <=table[startdate] +7 ),table[selection count])
@amitchandak Thats done it perfectly. I tidied it up a bit by adding a calculated end date column to my data but your solution was perfect.
Thanks you.
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 |
---|---|
115 | |
100 | |
88 | |
68 | |
61 |
User | Count |
---|---|
152 | |
120 | |
102 | |
87 | |
68 |