locked
Year-1 Calculation RRS feed

  • Question

  • Hello,

    Based on my data

    KPI 1 KPI 2 Month Qty Y date
    Value2 Finished Janv 2 100 01/01/2017
    Value1 Finished Janv 2 000 01/01/2017
    Stock Finished Janv 1 000 01/01/2017
    Stock Finished Fevr 29 000 01/02/2017
    Value2 Finished Fevr 29 000 01/02/2017
    Value1 Finished Fevr 0 01/02/2017
    Stock Finished Mars 1 200 01/03/2017
    Value1 Finished Mars 1 200 01/03/2017
    Value2 Finished Mars 0 01/03/2017
    Stock Finished Avril 41 509 01/04/2017
    Value1 Finished Avril 0 01/04/2017
    Value2 Finished Avril 0 01/04/2017
    Stock Finished Mai 2 739 01/05/2017

    I'am figuring out how to simply calculate the Quantity of the previous year 

    As you can see in my result, different varaible reach the goal.

    here are my calculation

    QTY-1MTDadd3:=CALCULATE(sum(Input[Qty Y]);ALL(Dates[Date]);DATEADD(Input[ladate];-1;YEAR))

    Somme QTY-1 is a sum of a data calculation for check.

    Question: As soon as I choose a year in the slicer, my variable display Nothing. This is the reason why I tried to filter on ALL(Dates) whee date is a calendar table.

    What am I doing wrong ?

    Friday, January 12, 2018 4:25 PM

Answers

  • Hello,

    Amazing comprehension of the model, and amazing demonstration.

    I tested theses calculations

    QTYY-1Sameperiod2:=CALCULATE(sum(Input[Qty Y]);SAMEPERIODLASTYEAR(Dates[Date]))

    QTY-1 OK DateAdd2:=CALCULATE(sum(Input[Qty Y]);DATEADD(Dates[Date];-1;YEAR))

    At the begining it didn't work.

    The problem was that I used, in the cross table, the date of the fact table instread of the date field of the date table.

    So bu doing this, it work now perfectly.

    Thanks a lot.

    I will have look to your web site.

    Jerome

    Monday, January 15, 2018 12:29 PM
  • It was the problem.

    Thanks a lot.

    Monday, January 15, 2018 12:30 PM

All replies

  • Hey Jerome,

    I recorded a video for you. Hope it helps.
    http://www.youtube.com/watch?v=lIBaLbCIZwg


    ⚡Power On!⚡
    -Avi Singh. Microsoft MVP. PowerBIPro

    Power BI Tutorial for Beginners (Step-by-Step) and more on YouTube.com/PowerBIPro

    More Power BI Training at www.LearnPowerBI.com

    YouTube.com/PowerBIPro

    Saturday, January 13, 2018 11:10 PM
  • I think this is simply that you are not using your date table for the input to DATEADD.

    QTY-1MTDadd3:=CALCULATE(sum(Input[Qty Y]);ALL(Dates[Date]);DATEADD(Input[ladate];-1;YEAR))

    try the folllowing:

    QTY-1MTDadd3:=CALCULATE(sum(Input[Qty Y]);ALL(Dates[Date]);DATEADD(Date[Date];-1;YEAR))


    http://darren.gosbell.com - please mark correct answers

    Sunday, January 14, 2018 8:06 PM
  • Hey Jerome,

    Thanks for your question.

    Avi and Darren has well explained this issue for you. If you want to know more about comparing equivalent periods in DAX, you can also refer to below blog:
    https://www.sqlbi.com/articles/compare-equivalent-periods-in-dax/


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, January 15, 2018 4:16 AM
  • Hello,

    I tried two solution that are wrong.

    QTY-1 OK DateAdd2:=CALCULATE(sum(Input[Qty Y]);ALL(Dates[Date]);DATEADD(Dates[Date];-1;YEAR))

    QTY-1 OK DateAdd3:=CALCULATE(sum(Input[Qty Y]);ALL(Dates[Date]);DATEADD(Dates[Date];-12;MONTH))

    Both of them give exactly the Quantity of the current year.

    Monday, January 15, 2018 9:10 AM
  • I'm not sure what's going on here as those calculations work fine for me. Is your slicer definitely coming from a Year column in your 'Dates' table? 


    http://darren.gosbell.com - please mark correct answers

    Monday, January 15, 2018 12:03 PM
  • Hello,

    Amazing comprehension of the model, and amazing demonstration.

    I tested theses calculations

    QTYY-1Sameperiod2:=CALCULATE(sum(Input[Qty Y]);SAMEPERIODLASTYEAR(Dates[Date]))

    QTY-1 OK DateAdd2:=CALCULATE(sum(Input[Qty Y]);DATEADD(Dates[Date];-1;YEAR))

    At the begining it didn't work.

    The problem was that I used, in the cross table, the date of the fact table instread of the date field of the date table.

    So bu doing this, it work now perfectly.

    Thanks a lot.

    I will have look to your web site.

    Jerome

    Monday, January 15, 2018 12:29 PM
  • It was the problem.

    Thanks a lot.

    Monday, January 15, 2018 12:30 PM