Tag Archives: DAX

Running total in DAX

Posted on by • Posted in Date functions • Tagged , • Leave a comment

To get a running total in DAX you can use the DATESYTD function.

Here is an example that gives running total per day for a given range.

EVALUATE
FILTER (
    ADDCOLUMNS (
        VALUES ( 'Date'[Date] ),
        "Running total", CALCULATE (
            'Internet Sales'[Internet Total Sales],
            DATESYTD ( 'Date'[Date] )
        ),
        "Sales per day", 'Internet Sales'[Internet Total Sales]
    ),
    'Date'[Date] < DATE ( 2006, 01, 20 )
        && 'Date'[Date] >= DATE ( 2006, 01, 01 )
)

And here is an example on how to get running total per month.

EVALUATE
FILTER (
SUMMARIZE (
'Date',
'Date'[Month Name],
'Date'[Calendar Year],
"Running total", CALCULATE (
'Internet Sales'[Internet Total Sales],
DATESYTD ( 'Date'[Date] )
),
"Sales per month", 'Internet Sales'[Internet Total Sales]
),
'Date'[Calendar Year] = 2006
)

DYNAMIC DATE FILTER IN DAX USING VBA

Posted on by • Posted in Date functions • Tagged , , , , , • Leave a comment

If you are creating scheduled reports in DAX it might be handy to add some VBA to calculate time periods automatically.

Here is an example that returns yesterday

EVALUATE
FILTER (
SUMMARIZE (
'Date',
'Date'[Date],
"Sales", 'Internet Sales'[Internet Total Sales]
),
	FORMAT('Date'[Date], "yyyy-MM-dd") = FORMAT(NOW() - 1, "yyyy-MM-dd")
)

If you want to test a valid date in the sample database you can figure out how many days you have to remove by running this SQL “select DATEDIFF(day, getdate(), ‘2006/05/18’)”. And then subtract this number instead of “NOW() – 1”.

And if you want to have a similar query for year this can look like this

EVALUATE
FILTER (
SUMMARIZE (
'Date',
'Date'[Calendar Year],
"Sales", 'Internet Sales'[Internet Total Sales]
),
	'Date'[Calendar Year] = INT(FORMAT(NOW() - 1, "yyyy"))
)

Creating an simple if test in DAX

Posted on by • Posted in Basic script • Tagged , , , • Leave a comment

Here is a sample on how to create an if test in DAX. It is really easy 🙂 In the sample below we test the sales per month for 2007 versus the sales per month for 2006. If the sales are higher we print “Higher”. If it was lower we print “Lower”.

DEFINE
    MEASURE 'Internet Sales'[Previous Sales per month] =
        CALCULATE (
            'Internet Sales'[Internet Total Sales],
            SAMEPERIODLASTYEAR ( 'Date'[Date] )
        )
    MEASURE 'Internet Sales'[Sales per month] =
        CALCULATE (
            'Internet Sales'[Internet Total Sales],
            'Date'[Month]
        )
    MEASURE 'Internet Sales'[MyTest] =
        IF (
            'Internet Sales'[Sales per month]
                >= 'Internet Sales'[Previous Sales per month],
            "Higher",
            "Lower"
        )
EVALUATE
FILTER (
    SUMMARIZE (
        'Date',
        'Date'[Month],
        'Date'[Calendar Year],
        "Previous Sales per month", FORMAT (
            'Internet Sales'[Previous Sales per month],
            "Currency"
        ),
        "Sales Sales per month", FORMAT ( 'Internet Sales'[Sales per month], "Currency" ),
        "Change", 'Internet Sales'[MyTest]
    ),
    'Date'[Calendar Year] = 2007
)

Get “Year To Date” and “Last Year To Date” in DAX

Posted on by • Posted in Date functions • Tagged , , , , , • Leave a comment

To get “Year To Date” and “Last year to date” in DAX is really simple. You can use the function TotalYTD and mix this with DATESBETWEEN or SAMEPERIODLASTYEAR.

This is an example with DATESBETWEEN

DEFINE
    MEASURE 'Internet Sales'[Previous Year To Date] =
        CALCULATE (
            TOTALYTD (
                'Internet Sales'[Internet Total Sales],
                DATESBETWEEN (
                    'Date'[Date],
                    FIRSTDATE ( DATEADD ( 'Date'[Date], -12, MONTH ) ),
                    LASTDATE ( DATEADD ( 'Date'[Date], -12, MONTH ) )
                )
            )
        )
    MEASURE 'Internet Sales'[Year To Date] =
        CALCULATE (
            TOTALYTD (
                'Internet Sales'[Internet Total Sales],
                'Date'[Date]
            )
        )
    MEASURE 'Internet Sales'[Change] =
        FORMAT (
            'Internet Sales'[Year To Date]
                / 'Internet Sales'[Previous Year To Date],
            "Percent"
        )
EVALUATE
FILTER (
    SUMMARIZE (
        'Date',
        'Date'[Date],
        "Previous Year To Date", 'Internet Sales'[Previous Year To Date],
        "Sales Year To Date", 'Internet Sales'[Year To Date],
        "Change", 'Internet Sales'[Change]
    ),
    'Date'[Date] = DATE ( 2007, 01, 15 )
)

And this is an example with SAMEPERIODLASTYEAR

DEFINE
    MEASURE 'Internet Sales'[Previous Year To Date] =
        CALCULATE (
            TOTALYTD (
                'Internet Sales'[Internet Total Sales],
                SAMEPERIODLASTYEAR ( 'Date'[Date] )
            )
        )
    MEASURE 'Internet Sales'[Year To Date] =
        CALCULATE (
            TOTALYTD (
                'Internet Sales'[Internet Total Sales],
                'Date'[Date]
            )
        )
    MEASURE 'Internet Sales'[Change] =
        FORMAT (
            'Internet Sales'[Year To Date]
                / 'Internet Sales'[Previous Year To Date],
            "Percent"
        )
EVALUATE
FILTER (
    SUMMARIZE (
        'Date',
        'Date'[Date],
        "Previous Year To Date", 'Internet Sales'[Previous Year To Date],
        "Sales Year To Date", 'Internet Sales'[Year To Date],
        "Change", 'Internet Sales'[Change]
    ),
    'Date'[Date] = DATE ( 2007, 01, 15 )
)

Define a measure. Make your code more readable

Posted on by • Posted in Basic script • Tagged , , , • Leave a comment

To make your code more readable you can create measures outside the evaluate statement. Let’s see an example on how we can do this.

Take a look at the query that returns sales per year versus sales last year.

EVALUATE
FILTER (
SUMMARIZE (
'Date',
'Date'[Calendar Year],
"Sales this year", SUM ( 'Internet Sales'[Sales Amount] ),
"Sales last year", CALCULATE(SUM ( 'Internet Sales'[Sales Amount] ),
SAMEPERIODLASTYEAR ( 'Date'[Date] ))
),
[Sales last year] <> 0
)

To move the calculation for “Sales last year” you can define a measure outside the evaluate statement like below. This can be very useful if you have lots of logic inside your query.

DEFINE
MEASURE 'Internet Sales'[Sales last year] =
CALCULATE ( SUM ( 'Internet Sales'[Sales Amount] ), SAMEPERIODLASTYEAR ( 'Date'[Date] ))

EVALUATE
FILTER (
SUMMARIZE (
'Date',
'Date'[Calendar Year],
"Sales this year", SUM ( 'Internet Sales'[Sales Amount] ),
"Sales last year", 'Internet Sales'[Sales last year]
),
[Sales last year] <> 0
)

If you want more than one measure you just add new measures below the first one. But the “DEFINE” should just written once at the top.

“Distinctcount” in DAX and MDX

Posted on by • Posted in Basic script • Tagged , , , , • Leave a comment

If you want to count distinct values in DAX you have at least two ways of doing this. You can use the distinctcount or you can use countrows with distinct.

A sample can be viewed below

EVALUATE
SUMMARIZE (
    'Product',
    "Number of products version 1", DISTINCTCOUNT ( 'Product'[Product Id] ),
    "Number of products version 2", COUNTROWS ( DISTINCT ( 'Product'[Product Id] ) )
)

In MDX the same thing can be done using this code

WITH MEMBER CountDistinct
AS COUNT(DISTINCT [Product].[Product Id].[Product Id])

SELECT {[Measures].[CountDistinct]} ON 0
FROM [Internet Operation]

You also have a distinctcount in MDX that can be used.

Add a subtotal with “Rollup”

Posted on by • Posted in Basic script • Tagged , , , • Leave a comment

If you use “Summarize” you can add a subtotal with the use of “Rollup”.

In the code below there is an example on how this can be done.

EVALUATE
FILTER (
    SUMMARIZE (
        'Internet Sales',
        ROLLUP ( 'Date'[Month Name] ),
        'Date'[Calendar Year],
        "Subtotal?", ISSUBTOTAL ( 'Date'[Month Name] ),
        "Sales", SUM ( 'Internet Sales'[Sales Amount] ),
        "Quantity", SUM ( 'Internet Sales'[Order Quantity] )
    ),
    'Date'[Calendar Year] = 2007
)

This query will give you sales and quantity per month. But you will also have a row with subtotal. To check if the row is a “Rollup” we can use the “ISSUBTOTAL”. You also have something called “Rollupgroup”.

In MDX you can write something like this to get a subtotal.

select {[Measures].[Internet Total Sales],[Measures].[Internet Total Units]} on 0,
[Date].[Calendar Year].&[2007] * [Date].[Month Name].AllMembers on 1
from [Internet Operation]

The subtotal will be in the “all” row. Depending on what you called the all level 🙂 You do not have any functionality like ISSUBTOTAL as far as I know.

Add a filter to your query using the “Filter” function

Posted on by • Posted in Basic script • Tagged , , , , • Leave a comment

It is really easy to add filters to your DAX query. In this post I will show you some samples.

Lets take a look at the following query:

EVALUATE
FILTER (
SUMMARIZE (
'Internet Sales',
'Date'[Calendar Year],
'Date'[Month Name],
"Sales", 'Internet Sales'[Internet Total Sales]
),
'Date'[Calendar Year] = 2007
)

This will give you sales per year per month for year 2007.

The MDX for the same resultset might look like this (you can of course move the year filter to the where clause)

SELECT [Measures].[Internet Total Sales] on 0,
[Date].[Calendar Year].&[2007] * [Date].[Month Name].[Month Name] on 1
from [Internet Operation]

In the next query I have added another filter

EVALUATE
(
FILTER(
SUMMARIZE
(
'Internet Sales',
'Date'[Calendar Year],
'Date'[Month Name],
"Sales", 'Internet Sales'[Internet Total Sales]
), 'Date'[Calendar Year] = 2007 && 'Internet Sales'[Internet Total Sales] > 500000
)
)

This new condition will remove all rows with sales less than 500000 for year 2007

The equal query in MDX will look like

SELECT [Measures].[Internet Total Sales] on 0,
FILTER([Date].[Calendar Year].&[2007] * [Date].[Month Name].[Month Name],[Measures].[Internet Total Sales] > 500000) on 1
from [Internet Operation]

The next sample will be a little more complex. It will give you sales per year per month compared to the years total. I am not sure if this is the most easy way of doing this. Or the best way 🙂

DEFINE
MEASURE 'Internet Sales'[Sum Year] =
CALCULATE (
'Internet Sales'[Internet Total Sales],
ALLSELECTED ()
)
MEASURE 'Internet Sales'[Percent of year] =
FORMAT (
CALCULATE (
'Internet Sales'[Internet Total Sales]
/ 'Internet Sales'[Sum Year]
),
"Percent"
)
EVALUATE
ADDCOLUMNS (
FILTER (
SUMMARIZE (
'Internet Sales',
'Date'[Calendar Year],
'Date'[Month Name],
"Sales", 'Internet Sales'[Internet Total Sales]
),
'Date'[Calendar Year] = 2007
),
"Year", 'Internet Sales'[Sum Year],
"Percent of year", 'Internet Sales'[Percent of year]
)

The get a similar resultset in MDX we can use this query

WITH MEMBER [Measures].[Sum Year] AS
( Root ( [Date].[Calendar Year] ),
[Measures].[Internet Total Sales] )
MEMBER [Measures].[Percent of year] AS
( [Measures].[Internet Total Sales] / [Measures].[Sum Year] ),
FORMAT_STRING = 'Percent'
SELECT
{ [Measures].[Internet Total Sales], [Measures].[Sum Year], [Measures].[Percent of year] } ON 0,
[Date].[Calendar].[Year].&[2007] * [Date].[Month Name].Children ON 1
FROM [Internet Operation]

Aggregate using “summarize”

Posted on by • Posted in Basic script • Tagged , , • Leave a comment

In DAX we have a function called “summarize”. This function aggregates totals over a set of groups.

Here is an example on how this function can be used:

 
EVALUATE
SUMMARIZE (
    'Internet Sales',
    'Date'[Calendar Year],
    'Date'[Month Name],
    "Sales", SUM ( 'Internet Sales'[Sales Amount] ),
    "Quantity", SUM ( 'Internet Sales'[Order Quantity] )
)

The code above will give you sales and quantity per month per year. The first argument is the table name. The second and third argument is the columns we want to order by. Fourth and fifth argument is the numbers you want to aggregate.

If you want to use some of the predefined measures you can skip the sum and write like this:

EVALUATE
SUMMARIZE (
    'Internet Sales',
    'Date'[Calendar Year],
    'Date'[Month Name],
    "Sales", 'Internet Sales'[Internet Total Sales],
    "Quantity", 'Internet Sales'[Internet Total Units]
)

If you manually want to set the order by clause this can be set like this:

EVALUATE
SUMMARIZE (
    'Internet Sales',
    'Date'[Calendar Year],
    'Date'[Month Name],
    "Sales", 'Internet Sales'[Internet Total Sales],
    "Quantity", 'Internet Sales'[Internet Total Units]
)
ORDER BY 'Date'[Month Name], 'Date'[Calendar Year]

The MDX that will give you the same result can look like this:

SELECT NON EMPTY [Date].[Calendar Year].[Calendar Year] * [Date].[Month Name].[Month Name] ON 1,
{ [Measures].[Internet Total Sales], [Measures].[Internet Total Units] } ON 0
FROM [Internet Operation]

And the SQL to get the similar resulat might look like this:

SELECT dimdate.calendaryear, 
       dimdate.englishmonthname, 
       Sum([salesamount])   AS 'Internet Total Sales', 
       Sum([orderquantity]) AS 'Internet Total Units' 
FROM   [factinternetsales] 
       INNER JOIN dimdate 
               ON dimdate.datekey = [factinternetsales].[orderdatekey] 
GROUP  BY dimdate.calendaryear, 
          dimdate.monthnumberofyear, 
          dimdate.englishmonthname

The result will be like this:

Summarize