Forum Discussion

abadi_89's avatar
abadi_89
Frequent Visitor
5 years ago
Solved

Relative changes graph with first period as base period

Hello guys. I am struggling with this one, my data is (Date, Product, Quantity, Value) What I need is a graph shows the average price relative change (Quantity/Value) and use the first period in th...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi  abadi_89 ,

    According to your description, I create this data：

     

    Here are the steps you can follow：

    1. Create measure.

    First find the percentage of the smallest month, convert the percentage of the smallest month to 100%, and compare the other months to change

    Measure =
var _Q=CALCULATE(SUM('Table'[Quantity]),FILTER('Table','Table'[date]=MAX('Table'[date])))
var _V=CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[date]=MAX('Table'[date])))
var _min=MINX(ALL('Table'),[date])
var _3Q=CALCULATE(SUM('Table'[Quantity]),FILTER(ALL('Table'),'Table'[date]=_min))
var _3V=CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),'Table'[date]=_min))
var _value=DIVIDE(_3Q,_3V)
var _percent=DIVIDE(1,_value)
var _result= DIVIDE(_Q,_V)*_percent
return _result

    2. Result

     

    You can downloaded PBIX file from here.

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

abadi_89 , If you do not have date then create a period table(separate) and create a rank on year period, And try measure like

 

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

 

diff = [This Period ]-[last Period ]
diff % = divide([This Period ]-[last Period ],[last Period ])

 

In case you have date then create a date table

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))

 

diff = [MTD Sales]-[last MTD Sales]
diff % = divide([MTD Sales]-[last MTD Sales],[last MTD Sales])

 

Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKA

 

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

  • abadi_89's avatar
    abadi_89
    Frequent Visitor
    5 years ago

    Hi again,

    Thank you for the help but it did not work! it still don't consider first period as base period for comparsion.

    I used the default sample data that comes with Power BI desktop, once you open blank Power BI desktop you see option "Data sample"

     

     

    • amitchandak's avatar
      amitchandak
      Icon for Super User rankSuper User
      5 years ago

      abadi_89 ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

      • abadi_89's avatar
        abadi_89
        Frequent Visitor
        5 years ago

        It won't let me post the table! here is a screen shot