DAX formulas to calculate MTD/QTD/YTD values and comparing them against same period last year MTD/QTD/YTD values in Power BI

Data source used for this post is the ‘Internet Sales’ data mart of the ‘AdventureWorksDW2017´ SQL server database.

DAX formulas to calculate the month-to-date sales amount are below. You can use one of the below two formulas and create the MTD measure in the fact table where the actual measure exists:

MTDSalesAmount1 = 
 IF(
       HASONEFILTER(DateDimension[Date]), 
       TOTALMTD(
              SUM('Internet Sales'[SalesAmount]),
                  'DateDimension'[Date]
               ), 
        SUM('Internet Sales'[SalesAmount])
 )
  
OR
MTDSalesAmount2 = 
 IF(
        HASONEFILTER(DateDimension[Date]), 
        CALCULATE(
               SUM('Internet Sales'[SalesAmount]), 
               DATESMTD(DateDimension[Date])
                 ) ,
 SUM('Internet Sales'[SalesAmount])
 )         

In the first formula, ‘TOTALMTD’ is a time-intelligence function in DAX that calculates the running month total of the current month in the context. Therefore, when using the ‘TOTALMTD’ function, second argument should be a ‘Date’ column, for the function to retrieve the ‘Dates’ that are less than or equal to the current date in the context to calculate the running total.

In the second formula, ‘DATESMTD’ is a time-intelligence function in DAX that retrieves the ‘Dates’ that are less than or equal to the current date in the context. ‘CALCUALTE’ DAX function then aggregates the ‘SalesAmount’ on all the dates listed by ‘DATESMTD’ function.

In the Power BI report screenshot below, could see the MTDSalesAmount1/MTDSalesAmount2 values on 02/Jan/2012 is sum of the ‘SalesAmount’ Value on first and second January and for the subsequent dates MTD values are sum of the Sales Amount values from the previous dates in the current month.

‘HASONFILTER’ DAX function on the above formulas will give the correct ‘Total’/’Grand Total’ values in the Power BI visualizations. Without the ‘HASONFILTER’ in the DAX formula, although the MTD sales amount is correctly calculated, the ‘Total’ / ‘Grand Total’ values will show the last MTD value on your visualization.  Let’s verify the results using the below formula without the ‘HASONFILTER’ DAX function.

MTDSalesAmount_TotalCheck = 
 TOTALMTD(
           SUM('Internet Sales'[SalesAmount]),
           'DateDimension'[Date]
         ) 

In the Power BI screenshot below, ‘Total’ value is same as the ‘December-2012’under the ‘MTDSalesAmount_TotalCheck’ column and doesn’t match with the total sales amount of the year.

In order to compare the MTD sales in the current context with the same period last year, use one of  the below DAX formulas.

LastYear_MTDSalesAmount1 = 
 CALCULATE(
            [MTDSalesAmount1],
            SAMEPERIODLASTYEAR(DateDimension[Date])
          )

 OR

LastYear_MTDSalesAmount2 = 
 CALCULATE(
              [MTDSalesAmount2],
              SAMEPERIODLASTYEAR(DateDimension[Date])
           ) 

In the Power BI screenshot below, ‘LastYear_MTDSalesAmount’ values for 2012 are exactly same as the ‘MTDSalesAmount1’ values in the 2011. Please note that , the visualization is filtered out to shown only the Calendar Year 2011 and 2012 data. Therefore, ‘LastYear_MTDSalesAmount’ values are blank in 2011.

Similarly, below formulas can be used for QTD and YTD values.

DAX formulas for the QTD calculations:

QTDSalesAmount1 = 
 IF(
     HASONEFILTER(DateDimension[Date]), 
     TOTALQTD(
               SUM('Internet Sales'[SalesAmount]),
               'DateDimension'[Date]
              ), 
      SUM('Internet Sales'[SalesAmount])
 )
  
 OR

QTDSalesAmount2 = 
 IF(
      HASONEFILTER(DateDimension[Date]), 
      CALCULATE(
                  SUM('Internet Sales'[SalesAmount]), 
                  DATESQTD(DateDimension[Date])
               ) ,
      SUM('Internet Sales'[SalesAmount])
 ) 

DAX formulas for the previous year QTD calculations:

LastYear_QTDSalesAmount1 = 
 CALCULATE(
             [QTDSalesAmount1],
             SAMEPERIODLASTYEAR(DateDimension[Date])
          )

 OR

 LastYear_QTDSalesAmount2 = 
 CALCULATE(
             [QTDSalesAmount2],
             SAMEPERIODLASTYEAR(DateDimension[Date])
          ) 

DAX formulas for the YTD calculations:

YTDSalesAmount1 = 
 IF(
      HASONEFILTER(DateDimension[Date]), 
      TOTALYTD(
                 SUM('Internet Sales'[SalesAmount]),
                 'DateDimension'[Date]
              ), 
      SUM('Internet Sales'[SalesAmount])
  )
  
 OR

YTDSalesAmount2 = 
 IF(
         HASONEFILTER(DateDimension[Date]), 
         CALCULATE(
                    SUM('Internet Sales'[SalesAmount]), 
                    DATESYTD(DateDimension[Date])
                 ) ,
         SUM('Internet Sales'[SalesAmount])
 ) 

DAX formulas for the previous year YTD calculations:

LastYear_YTDSalesAmount1 = 
CALCULATE(
           [YTDSalesAmount1],
           SAMEPERIODLASTYEAR(DateDimension[Date])
         )

 OR

 LastYear_YTDSalesAmount2 = 
 CALCULATE(
            [YTDSalesAmount2],
            SAMEPERIODLASTYEAR(DateDimension[Date])
          ) 

Hope this will post help you in using the running totals in Power BI.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s