calculate quants between years

[davidi4524]

i have a query from sql server.

the columns is:

custname

month

year

partname

totalquant

if i whant to select in power bi custname, and select partname and bench between years 2016 and 2017 by total quant

somting like this:

custname -    year   -     partname      -  totalquant 2016     - totalquant 2017      - over/under

"microsoft"    2016        "iphone 7"     -     100                    -        180                   -        80

what do i need to do??

thanks.

[dedelman_clng]

The following 3 measures should work for you.  When placed in a visual they should respond to the row filters (customer, product)

Total Quant 2016 = 
SUMX ( FILTER (ALL(Table), Table[Year] = 2016 ), Table[totalquant])

Total Quant 2017 = 
SUMX ( FILTER (ALL(Table), Table[Year] = 2017 ), Table[totalquant])

Over/Under = [Total Quant 2017] - [Total Quant 2016]

If this doesn't work, please provide some mocked up raw data for further evaluation.

Hope this helps,

David

[davidi4524]

hi , first thanks to the responde.

the measure is not respond to the filter , not for custname filter and not to partname filter.

what to do to fix this?

thanks.

[v-huizhn-msft]

Hi @davidi4524,

You can create calculated column using the following formula, use year and it as filter.

amount in one year=CALCULATE(SUM(Table[Totalquant]),ALLEXCEPT(Table,Table[year]))

Then please create the measure get difference between year.

If this is not want you want, please post some sample data for further analysis.

Best Regards,
Angelia

[davidi4524]

and if i need the amount for each year by month? 

[v-huizhn-msft]

Hi @davidi4524,

Please try the following formula and check if it works fine.

amount in one year=CALCULATE(SUM(Table[Totalquant]),ALLEXCEPT(Table,Table[year],Table[month]))

Best Regards,
Angelia