4 Replies Latest reply: Jan 29, 2016 8:22 AM by Betty Habtemariam RSS

    Gross Margin YTD and LYTD

    Betty Habtemariam

      Hi guys,

       

      I'd like to know if someone could help me with the calculation of YTD and LYTD of Gross Margin? I've done one for YTD and LYTD sales and would really be happy if I also could show for the Gross Margin =D Please help!

       

      Sincerely yours,

      Betty Habtemariam

        • Re: Gross Margin YTD and LYTD
          jagan mohan rao appala

          Hi,

          Check formula for the Gross Margin.  Generally Gross Margin = Sales Price - Cost Price.

           

          How to Calculate Gross Profit Margin Percentage | Chron.com

           

          Regards,

          Jagan.

          • Re: Gross Margin YTD and LYTD
            Susovan Ghosh

            Hi,

            Check this expression for Gross Margin YTD :

            =sum({<@_Date={">=$(=Date(YearStart(Max(@_Date),0,4),'DD/MM/YYYY'))<=$(=Date(MonthEnd(Max(@_Date)),'DD/MM/YYYY'))"},@_Month=,@_FinQuarter=,@_FinFullYear=>} #_GROSS_PROFIT)

            This Expression for Gross Margin LYTD :

            =sum({<@_Date={">=$(=Date(YearStart(Max(@_Date),-1,4),'DD/MM/YYYY'))<=$(=Date(MonthEnd(Max(@_Date),-12),'DD/MM/YYYY'))"},@_Month=,@_FinQuarter=,@_FinFullYear=>} #_GROSS_PROFIT)

            • Re: Gross Margin YTD and LYTD
              Betty Habtemariam

              Hi,

               

              I just wanted to say that I found my solution a while back and just wanted to post my solution here in case a novice in the future wants to know how to do it. Before you read any further, it's good to know that the calculation for Gross Margin wasn't done in the script, but in the front end.


              Total Gross Margin % (for all years) is:

              Sum([Gross Profit])/Sum(Sales).

               

              And for the Gross Margin % this year and last year, I used a set analys of course. Take a look down below!

               

              The expression for Gross Margin % this year is :

              sum({<Year = {'$(=Max(Year))'}>}[Gross Profit]) / sum({<Year = {'$(=Max(Year))'}>} Sales).

               

              The expression for Gross Margin % Last year is :

              sum({<Year = {'$(=Max(Year) -1)'}>}[Gross Profit]) / sum({<Year = {'$(=Max(Year) -1)'}>} Sales).

               

              The expression for the variance between those two is:

              sum({<Year = {'$(=Max(Year))'}>}[Gross Profit]) / sum({<Year = {'$(=Max(Year))'}>} Sales)

              -

              sum({<Year = {'$(=Max(Year) -1)'}>}[Gross Profit]) / sum({<Year = {'$(=Max(Year) -1)'}>} Sales)


              Remember that this exact script works IF the Gross Margin isn't calculated in the script, but in the front end. That's why the set analys is used on both aggregations. If Gross Margin % is calculated in the script, the expression would've looked something like this: Sum({<Year = {'$(=Max(Year))'}>} [Gross Margin]), which is for this year.

               

              Okay, have a nice weekend!

               

              Sincerely,

              Betty Habtemariam