cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
rmeng Regular Visitor
Regular Visitor

Cards with first and last year values

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))

Sem Título.jpg

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Floriankx Established Member
Established Member

Re: Cards with first and last year values

 Hello,

 

you can adapt @ThomasFoster' 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.

View solution in original post

3 REPLIES 3
ThomasFoster Established Member
Established Member

Re: Cards with first and last year values

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] ) )
)
rmeng Regular Visitor
Regular Visitor

Re: Cards with first and last year values

Don´t work because the column Date Year is a Dimension Table.

Sem Título.jpg

 

Sem Título2.jpg

 

 

 

 

 

Floriankx Established Member
Established Member

Re: Cards with first and last year values

 Hello,

 

you can adapt @ThomasFoster' 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.

View solution in original post

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (1,176)