Rewrite dynamic T-SQL date variables in DAX-CodePudding

We're currently rebuilding basic emailed reports built using T-SQL to be paginated reports published on Power BI. We're muddling through by creating the tables we need with the appropriate filters in Power BI Desktop to reconcile the numbers, then taking the DAX code from them using the Performance Analyser.

The one I'm working at the minute has a simple bit of SQL code to get data for a previous calendar month. I have no idea how or if it's possible for this to exist in DAX?

-- Validation to get previous month
IF (MONTH(GETDATE()) - 1) > 0 
SET @MONTH = MONTH(GETDATE()) - 1
ELSE
SET @MONTH = '12'

-- Validation to get year of previous month
IF (@MONTH < 12) 
SET @YEAR = YEAR(GETDATE())
ELSE
SET @YEAR = YEAR(GETDATE()) - 1

-- Set start date and finish date for extract
SET @PERIOD = @YEAR   RIGHT('00'   @MONTH, 2)

It needs to become a hidden SSRS parameter or just inline code to be used with this DAX variable:

VAR __DS0FilterTable = 
TREATAS({"202212"}, 'Org View_VaultexCalendar'[Calendar Month No])

So the "202212" would become @period or the equivalent if doable without a parameter.

CodePudding user response：

SSRS Parameter:

=IIF(
    Month(Today()) > 1,
    Year(Today()) & RIGHT("00" & Month(Today()) - 1, 2),
    Year(Today())-1 & "12"
)

DAX expression:

IF (
    MONTH ( TODAY () ) > 1,
    YEAR ( TODAY () ) & FORMAT ( MONTH ( TODAY () ) - 1, "00" ),
    YEAR ( TODAY () ) - 1 & "12"
)

In both cases we look at today's month and check if it's after January. If it is, take the current year and concatenate it with the current month less one and padded with a leading zero when needed. In the other case we know that the month is January so take the current year less one and concatenate it with "12"