Tag Archives: FORMAT

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"))
)

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 )
)