How to calculate MTD GM%

[Maahmohammed]

Dears, 

I have used the code below in developing MTD for Sales:

Sales_ADS-MTD =

VAR _Date = today()

VAR _Max_Date = Max('1_Calendar'[0_Date])

VAR _Compared_Date = Min(Date(Year(_Max_Date),Month(_Max_Date),Day(_date)),_Max_Date)

Return

Calculate(

          AVERAGEX(DATESMTD(

                               CALCULATETABLE(

                                               VALUES('1_Calendar'[0_Date]),

                                                KEEPFILTERS('1_Calendar'[0_Date]<=_Compared_Date)

                                             )

                             ),

                             '0_Measures_Sales'[0_Sales_5_Total]

                   )

But as you know, in case of GM%, we couldn't calculate the average of %, so, how to calculate the MTD GM% MTD?

Regards

         )

[Anonymous · Accepted solution]

Hi @Maahmohammed ,

If you want to calculate GM%, you can calculate it by dividing the cumulative gross profit by the cumulative sales, here is the DAX I wrote I hope it helps.

GM_MTD = 
VAR D=MAX('Table'[Date])
VAR F=DATE(YEAR(D),MONTH(D),1)
VAR B=MIN(DATE(YEAR(D),MONTH(D),DAY(TODAY())),D)
VAR cumulativeprofit=
   CALCULATE(
       SUM('Table'[Gross_Margin]), 
      FILTER(ALL('Table'),
    'Table'[Date]>=F&&'Table'[Date]<=B))
VAR Cumulativesales= CALCULATE(
       SUM('Table'[Sales_5_Total]),
       FILTER(ALL('Table'),
       'Table'[Date]>=F&&'Table'[Date]<=B
       )
)

RETURN
DIVIDE(cumulativeprofit,Cumulativesales,0)

If you have any other questions, check out the pbix file I've uploaded, I'd be incredibly proud to help you out!

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

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