Forum Discussion

mcha17's avatar
mcha17
Regular Visitor
3 years ago

Monthly YoY comparison

I have the following data and am trying to calculate the "YoY Change" in Power Query, but am not having any luck.  Any help is appreciated.
 
Month is a text field, but I can also convert to a date field if that makes things easier.    Also, I am comparing the months for only 2 years (2023 vs 2024).  I also want the measure values for months in 2024 in the pivot table output, since there are no differences for 2023. 

 
Input

MonthValue
202201500
2022021000
2022032000
202301100
2023022000
202303500

Output
MonthYoY Change
202301-1500
2023021000
202303-500

 

@NeedHelp

1 Reply

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

    mcha17 , Create a date from the month

    Date =  date(left([Month],4) , right([Month],2) , 1)

     

    Have all required columns in the date table and join it with your date now 

     

    Then you can use TI

     

    Year behind Sales = CALCULATE(SUM(Table[Value]),dateadd('Date'[Date],-1,Year))
    Year behind Sales = CALCULATE(SUM(Table[Value]),SAMEPERIODLASTYEAR('Date'[Date]))

     

    MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))


    last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))
    Previous year Month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth(dateadd('Date'[Date],-11,MONTH)))

     

     

    Else create a new table with Month(You have call it year month) , Year and month (number 1 - 12) , Assume name is date table

     

    Join it back with your table 

     

    This Month = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year]) && Date[Month]=max(Date[Month])))
    Last year same Month= CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year])-1 && Date[Month]=max(Date[Month])))

     

     

    calendar

    Calendar = Addcolumns(calendar(date(2020,01,01), date(2021,12,31) ), "Month no" , month([date])
    , "Year", year([date])
    , "Month Year", format([date],"mmm-yyyy")
    , "Month year sort", year([date])*100 + month([date])
    , "Qtr Year", format([date],"yyyy-\QQ")
    , "Qtr", quarter([date])
    , "Month",FORMAT([Date],"mmmm")
    , "Month sort", month([DAte])
    , "FY Year", if( Month(_max) <7 , year(_max)-1 ,year(_max))
    , "Is Today" ,if([Date]=TODAY(),"Today",[Date]&"")
    ,"Day of Year" , datediff(date(year([DAte]),1,1), [Date], day)+1
    , "Month Type", Switch( True(),
    eomonth([Date],0) = eomonth(Today(),-1),"Last Month" ,
    eomonth([Date],0)= eomonth(Today(),0),"This Month" ,
    Format([Date],"MMM-YYYY") )
    ,"Year Type" , Switch( True(),
    year([Date])= year(Today()),"This Year" ,
    year([Date])= year(Today())-1,"Last Year" ,
    Format([Date],"YYYY")
    )
    )

     

    Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510
    Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
    Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

     

    https://community.powerbi.com/t5/Community-Blog/Creating-Financial-Calendar-Decoding-Date-and-Calendar-1-5-Power/ba-p/1187441
    https://medium.com/chandakamit/cheat-sheet-calendar-of-any-standard-month-just-one-variable-apart-5ee47de6a208
    https://amitchandak.medium.com/cheat-sheet-power-query-financial-year-calendar-5ceaacb520f1