DAX Custom Financial Periods

0
votes

I have an SSAS Tabular model that I am attempting to use a DAX expression to calculate Year over Year metrics on. I have a table called fact_transaction and a table called dimdate that are relevant to the calculation I am trying to do. The dimdate table has a date key, a date field, a Year field, a month field, a period field and a quarter field. As A result of needing to use the financial Periods rather than months, SAMEPERIODLASTYEAR will not work for my calculations.

The financial periods do not correspond directly to months of the year and can vary in length by up to 2 days. Is there any way to calculate this without getting too convoluted? within a SQL query I would just join it to itself with [Year] = [YEAR] -1 and Period = Period (Along with the various account fields used in the relationships). Not particularly sure how to do this in DAX.

1
sql-serverssasdaxssas-tabular

1 Answers

0
votes

Yes, you should be able to do something very similar in DAX with filters. It's hard to write exactly what you need without more details, but maybe try something like this:

YoY Metric =
VAR CurrentYear = SELECTEDVALUE(DimDate[Year])
VAR CurrentPeriod = SELECTEDVALUE(DimDate[Period])
RETURN CALCULATE([Expression],
           FILTER(ALL(DimDate),
               DimDate[Year] = CurrentYear - 1 &&
               DimDate[Period] = CurrentPeriod))

Edit

If SELECTEDVALUE is not available, then define the variables like this:

VAR CurrentYear = IF(HASONEVALUE(DimDate[Year]), VALUES(DimDate[Year]))
VAR CurrentPeriod = IF(HASONEVALUE(DimDate[Period]), VALUES(DimDate[Period]))