24 Jun’15

BIPowerful DAX CALCULATE() Function

The CALCULATE function in DAX is the magic key for many calculations we can do in PowerPivot.

Below is the syntax:

CALCULATE( <expression>, <filter1>, <filter2>… )

The expression that we put in the first parameter has to be evaluated to return the result (i.e. a value, not a table). For this reason, the expression is usually an aggregation function like SUM, MIN, MAX, COUNTROWS and so on.

This expression is evaluated in a context that is modified by the filters. A key point is that these filters can both enlarge and restrict the current context of evaluation. Let’s try to understand what it means by considering a few examples.

The following data model we have imported in PowerPivot named ‘Contract’ & ‘Project’

Scenario 1
Compare Contract & Project data model on YearMonth Column and take sum of multiple records of revenue column of Project data model into Contract data model
Project data model has StartYM & StartRevenue Column as shown below


And Contract data model has YM column, using Project data model StartYM, StartRevenue columns & Contract data model YM column, here we have derived StartR column with the help of Calculate() DAX function as shown below


Formula is
Scenario 2
Calculate running total of ToDo column in ‘Contract-ToDo’ data model on basis of YearMonth column as shown below


Formula is
=calculate(sum(‘Contract-ToDo'[ToDo]),filter(‘Contract-ToDo’,’Contract-ToDo'[YearMonth] <= EARLIEST('Contract-ToDo'[YearMonth])))

Written by

Team Member


Leave a Reply

Your email address will not be published. Required fields are marked *

Time limit is exhausted. Please reload the CAPTCHA.

Want to streamline your business processes?

  • This field is for validation purposes and should be left unchanged.

Recent Articles

  • Managing mailbox through Email Archiving

    20 March’ 2018

    Introduction: Email can fill your Outlook Inbox quickly – new messages, replies, and forwards. Before you know it, yo...

    Read more
  • Workaround to Report.SAVEASPDF in NAV 2018

    20 March’ 2018

    Objective: In NAV 2017 Emailing the PDF by running the report using Report.SAVEASPDF is now not allowed in NAV 2018. Th...

    Read more
  • Dynamics 365 Client Diagnostics

    15 March’ 2018

    Main cause of Performance Issues: Bandwidth and latency are the primary characteristics which affects the performance o...

    Read more
  • Closing Blind Shift in MPOS and CPOS in Dynamics 365 for Retail

    15 March’ 2018

    Manual For closing Blind shift in POS: Blind shifts terminate the currently ongoing shift and when logged in a new shif...

    Read more
  • Persistent Filters in the Power BI Service

    13 March’ 2018

    Introduction: The feature is finally release, and it is power BI has announced general availability of persistent filt...

    Read more