Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Delta between two measures - how to create it?

Dear All,


I have a challenge with creating measure which will calculate delta between Emission Per Ton measure.

Emission Per Ton = Quantity / Measure per row for filtered period.

I would like to create a measure which calculates difference between [EmissionPerTon] for 2019 and [EmissionPerTon] for 2020-Q1.

 

Do you have any idea to solve the problem? It seems to be easy but for me it is not...

Thank you in advance for your help!

Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    6 years ago

    hi  Anonymous 

    Add a rank/index column for 2019,2020Q1 column, then use ALL function to get the previous row measure.

    Try this way as below:

    Step1:

    Add a rank/index column for 2019,2020Q1 column

    Step2:

    Create a difference measure as below:

    Difference = [Emission Per Ton]-CALCULATE([Emission Per Ton],FILTER(ALL('Dim period'),'Dim period'[Index]=MAX('Dim period'[Index])-1))

    Result:

     

    and here is my simple sample pbix file, please try it.

     

    Regards,

    Lin

2 Replies

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

    Anonymous , This Qtr vs last year or last to the last qtr vs last year. if you have a date

    example

    QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))

Last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,QUARTER)))

Last complete QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD( ENDOFQUARTER(dateadd('Date'[Date],-1,QUARTER))))
Last to last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-2,QUARTER)))
Next QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],1,QUARTER)))
Last year same QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,Year)))
Last year same QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(ENDOFQUARTER(dateadd('Date'[Date],-1,Year))))
trailing QTR = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,QUARTER))
trailing  4 QTR = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-4,QUARTER))
CALCULATE([Total Value], PREVIOUSQUARTER('Calendar'[Date]))


YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
//Only year vs Year, not a level below

This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))

     

    Power BI — YTD Questions — Time Intelligence 1–5
    https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
    Power BI — QTD Questions — Time Intelligence 2–5
    https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839

     

    To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
    https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
    https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
    https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

    See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


    Appreciate your Kudos.

     

     

  • v-lili6-msft's avatar
    v-lili6-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    hi  Anonymous 

    Add a rank/index column for 2019,2020Q1 column, then use ALL function to get the previous row measure.

    Try this way as below:

    Step1:

    Add a rank/index column for 2019,2020Q1 column

    Step2:

    Create a difference measure as below:

    Difference = [Emission Per Ton]-CALCULATE([Emission Per Ton],FILTER(ALL('Dim period'),'Dim period'[Index]=MAX('Dim period'[Index])-1))

    Result:

     

    and here is my simple sample pbix file, please try it.

     

    Regards,

    Lin