Forum Discussion

abukapsoun's avatar
abukapsoun
Post Patron
5 years ago
Solved

Calculation support

Hi Gents,   Need your support with the following:   I have this table     Type P01 P.. P12 CountryX Sales Coffee       CountryX Sales Margin Coffee       CountryX Sal...
Need Help
  • CNENFRNL's avatar
    CNENFRNL
    5 years ago

    Hi, abukapsoun , how about calculating number of quarter after unpivoting P01-P12. The sales data then is tranformed to a one-dimensional table. A pbix file is attached for your reference.

amitchandak's avatar
amitchandak
Super User
5 years ago

abukapsoun , First of unpivot the table. Then Create Period table With Period and Qtr , if you have the year you can add that. Or Create a year period table. Create period rank if needed qtr rank .

 

new column in period table

Qtr = Switch (true(),

[period] in {"P01","P02","P03"}, "1",

[period] in {"P04","P05","P06"}, "2"

///keep on adding

}

 

Here Date is your period table

 

Month Rank = RANKX(all('Date'),'Date'[Period],,ASC,Dense)  // period or year -period
This Month = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])))
Last Month = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])-1))
Last year Month= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=(max('Date'[Month Rank]) -12)))

 

Qtr or Qtr Rank

 

Qtr Rank = RANKX(all('Date'),'Date'[Qtr],,ASC,Dense)  //on Qtr or Year - Qtr
This Qtr = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank])))
Last Qtr = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank])-1))