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 have the following tables in my data model (using directquery). I want to calculate the value in the ValueTable between the StartDate and EndDate.
ValueTable:
Date Value
25-2-2017 1
26-2-2017 1
27-2-2017 1
28-2-2017 1
1-3-2017 1
2-3-2017 1
3-3-2017 1
4-3-2017 1
5-3-2017 1
PeriodTable:
StartDate EndDate
1-2-2017 28-2-2017
1-3-2017 31-3-2017
The outcome of the formule should look like this:
1-2-2017 - 28-2-2017 = 4
1-3-2017 - 31-3-2017 = 5
Could someone help me with this?
Solved! Go to Solution.
Hi @rolf1994,
You should be able to use the formula below to create a measure in this scenario, then show the measure on the Table/Matrix visual with PeriodTable[StartDate] and PeriodTable[EndDate] column.
Measure = CALCULATE ( SUM ( ValueTable[Value] ), FILTER ( ValueTable, ValueTable[Date] >= MIN( PeriodTable[StartDate] ) && ValueTable[Date] <= MAX ( PeriodTable[EndDate] ) ) )
Regards
ok, i found my way.
Its not elegant, but works.
First I created two measures:
Column = CALCULATE(SUM(ValueTable[Value]),FILTER(ValueTable,ValueTable[Date]>PeriodTable[StartDate] && ValueTable[Date]<PeriodTable[EndDate]))
Thanks for your answer. i get the following error when i try to create the measure:
A single value for column 'StartDate' in table 'PeriodTable' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result
I created a column and it was in the PeriodTable table.
Hi @rolf1994,
You should be able to use the formula below to create a measure in this scenario, then show the measure on the Table/Matrix visual with PeriodTable[StartDate] and PeriodTable[EndDate] column.
Measure = CALCULATE ( SUM ( ValueTable[Value] ), FILTER ( ValueTable, ValueTable[Date] >= MIN( PeriodTable[StartDate] ) && ValueTable[Date] <= MAX ( PeriodTable[EndDate] ) ) )
Regards
This worked well for me thank you 🙏 @v-ljerr-msft just have one additional question. How would you write this to show the output as 0 for counts where there is no date values just yet?
I have the same but opposite setup on my tables, how would you write the measure if the periods are on the values table and the and the second table is a single date point, where you wanted to get the sum of all values where the single date falls in-between the start and end dates?
ie.
Date Table:
Date
1/15/2018
2/15/2018
3/15/2018
Values Table
Start | End | Value
1/01/18 | 1/31/18 | 1
1/13/18 | 3/12/18 | 1
2/14/18 | 3/13/18 | 1
so i would be looking for a result like this:
Date | Sum
1/15/18 | 2
2/15/18 | 2
Thanks
(let me know if this is too offtopic and I will start a new thread)
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |