Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I want 2 cards with the value from the first year and final year considering that:
Because there are years with blank values->
Inicial Year= calculate(min(date[year]);filter(table;[values]>0))
Final Year= calculate(max(date[year]);filter(table;[values]>0))
Solved! Go to Solution.
Hello,
you can adapt @Anonymous' solution
Min Data1 =
VAR MinYear = MIN(DateTable[Year])
CALCULATE ( SUM ( Table1[data1] ), FILTER ( DateTable, DateTableYear = MinYear) )
Max is similar. Give it a try a let us know.
I have set the following table to use as an example
Table 1
Date, data1, data2
1 jan 2011,1,13
1 feb 2011,2,14
1 mar 2011,3,15
1 jan 2012,4,16
1 feb 2012,5,17
1 mar 2012,6,18
1 jan 2013,7,19
1 feb 2013,8,20
1 mar 2013,9,21
1 jan 2014,10,22
1 feb 2014,11,23
1 mar 2014,12,24
The following measures then give the values you were looking for
Min Data1 = CALCULATE ( SUM ( Table1[data1] ), FILTER ( Table1, Table1[date] = MIN ( Table1[date] ) ) )
Max Data2 = CALCULATE ( SUM ( Table1[data2] ), FILTER ( Table1, Table1[date] = MAX ( Table1[date] ) ) )
Don´t work because the column Date Year is a Dimension Table.
Hello,
you can adapt @Anonymous' solution
Min Data1 =
VAR MinYear = MIN(DateTable[Year])
CALCULATE ( SUM ( Table1[data1] ), FILTER ( DateTable, DateTableYear = MinYear) )
Max is similar. Give it a try a let us know.