Sales_OldCustomer = CALCULATE ( SUM ( Sales[Amount in USD] ), FILTER ( Sales, YEAR ( Sales[Date] ) RELATED ( Customer[Created Year] ) ))Sales_NewCustomer = CALCULATE ( SUM ( Sales[Amount in USD] ), FILTER ( Sales, YEAR ( Sales[Date] ) = RELATED ( Customer[Created Year] ) ))

My friend and coworker Melissa Coates (aka @sqlchick) messaged me the other day to see if I could help with a DAX formula. She had a Power BI dashboard in which she needed a very particular interac…

DAX calculations can leverage relationships present in the data model, but you can obtain the same result without physical relationships, applying equivalen

Easy way to create typical finance reports like Profit and Loss using DAX that (unlike other solutions I've seen so far) can be handled with very basic know

Recently I was helping someone on the Power BI community who wanted to only see the sales for the last 12 months in his chart, nothing more. Well this is pretty simple using DAX and I decided to us…

I wish I had an awesome intro for this.  I was mostly excited to write this entry… though, the techniques are fairly well known.  I totally have no problem repeating good techniques, because… you are going to forget you read it in some other blog 2 years ago anyway   But as I got into …

Introduction I was wondering whether I could add predictive analytics into my PowerPivot Workbooks. If I could predict the sales base...

I was extremely pleased to see that there was a Crossjoin() function in DAX, if only because it meant that I wouldn’t have to rename my company. Let’s see how to use it… The Crossjoin() function re…

Following on from my last post, let’s now see how we can do group-by style in DAX queries. The key to aggregating data is the Summarize() function, which is broadly similar to a Group By in SQL. So…

Information about Microsoft SQL Server Analysis Services (SSAS) 2005/2008 and MDX - News, Blogs, FAQs, Tools, Articles, Software, Books, Learnings, Webcasts, Scripts

In this article, Alberto Ferrari describes a new efficient way to compute returning customers in DAX thanks to an idea suggested by a student attending an O

Today’s post is about different ways to set a simple filter in DAX. Although there are plenty of writings covering these patterns, I still see DAX authors get confused on a regular basis. In …

A few days ago I published the article FILTER vs CALCULATETABLE: optimization using cardinality estimation , where I try to explain why the sentence “CALCULATETABLE is better than FILTER” is not always true. In reality, CALCULATETABLE internally might

Posts about DAX function ADDCOLUMNS written by digvendra

Creating a copy of a column with a new name is pretty simple in DAX: you can just use the AddColumns() function. For example if we take a model with the DimProductCategory table from Adventure Work…

Hey guys,   I am using PowerBi in my company to create some dashboards, and I need to incorporate a calculated column in my data set that calculates the following : 1/ The difference in Days between column [StartDate] and [EndDate] 2/ However if the [EndDate] row is empty, it should use (Today) as a...

Guest post by David Churchward Gantt Hours measure debugging with DAX Studio – isn’t it pretty! At the end of my last post in the Gantt Chart series, GANTT

Try this... Total in Previous Period 3 = VAR thisperiod = MAX('Table'[Index]) RETURN CALCULATE ( [Total Measure], ALLEXCEPT('Table', 'Table'[Customer]), 'Table'[Index] = thisperiod - 1) I think these are the results you are looking for...

All measures - the last one gives us the right number for the grand total (i hope that's what it does!)

newDuration = VAR TotalSeconds=SUMX('Sheet2 (2)',HOUR('Sheet2 (2)'[CopyDuration])*3600+MINUTE('Sheet2 (2)'[CopyDuration])*60+SECOND('Sheet2 (2)'[CopyDuration]))VAR Days =TRUNC(TotalSeconds/3600/24)VAR Hors = TRUNC((TotalSeconds-Days*3600*24)/3600)VAR Mins =TRUNC(MOD(TotalSeconds,3600)/60)VAR Secs = MOD(TotalSeconds,60)return IF(DAYS=0,"",IF(DAYS1,DAYS

This session is a natural continuation of DAX 101 and aims to teach you some of the advanced features of DAX. Alberto will explain in detail the concepts of

Hello !    I don't understand why my measure in Power Bi for Desktop doesn't work : it is a calculate function with a filter function (as I saw here: https://msdn.microsoft.com/fr-fr/library/dn727121.aspx )   As you can see below, I would like to see the sum of amount only if the day corresponds to ...

A data model for DAX has three numeric data types: integer, floating point, and fixed decimal number. This article describes them and explains why the fixed

A lot of times you just to show values of rows from the current month compared to other specific time periods, like previous month, ytd, previous YTD, etc. The problem is, how do you determine the