Forum Discussion

sorg's avatar
sorg
New Member
6 years ago
Solved

Total newbie need some help.

Hi,

I am a total beginner.

I am starting to tinker a little bit with power BI in order to create some KPI for my job, but i am struggling a little bit.

Here is my current issue.

 

I have a table in which i have a reccord of all the quotation issued by my company. The simplified structure of this table is as follows:

date       idProject idVersion netPrice
01/02/2018 0001      10        1111
18/06/2018 0001      20        2222
21/06/2019 0001      30        3333
30/08/2019 0002      10        4444
30/09/2019 0002      20        5555

I want to create a report that display , for a given period, the list of the sent quotation keeping only the latest version of each project in the given period.

 

For exemple, if i filter on 2018, i expect to see:

date idProject idVersion netPrice
18/06/2018 0001 20 2222

if i filter on 2019, i expect to see:

date idProject idVersion netPrice
21/06/2019 0001 30 3333
30/09/2019 0002 20 5555

I have imported my table in power BI, and i started creating a report.

I have a filter that allow to select the period. It work as expected.

I have created a "table display" with the following values: date, idProject and a quick measure making the following calculation:

lastIdVersionInThePeriod = 
MAXX(
KEEPFILTERS(VALUES('Table'[idProject]));
CALCULATE(MAX('Table'[idVersion ]))
)

 

it works quite well, and i get this (if i filter on 2019):

 

date idProject idVersion
21/06/2019 0001 30
30/09/2019 0002 20

If i add the netPrice value in the table, it does not work anymore, and it will display all the version in the period (not only the last one)

I suspect, i am not doing it properly, 

Can someone point me in the right direction ?

Need Help
  • parry2k's avatar
    parry2k
    6 years ago

    sorg try this measure 

     

    Measure = 
VAR __id = MAX ( 'Table'[id] )
VAR __date = CALCULATE ( MAX( 'Table'[date] ), ALLSELECTED ( 'Table' ),  'Table'[id] = __id ) 
RETURN CALCULATE ( MAX ( 'Table'[netprice] ), VALUES ( 'Table'[id] ), 'Table'[id] = __id, 'Table'[date] = __date )

     

    and you will get this output.

     

    Would appreciate Kudos 🙂 if my solution helped.

     

4 Replies