0

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.

S Grzybowski
  • 115
  • 1
  • 6

1 Answers1

0

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]))
Alexis Olson
  • 38,724
  • 7
  • 42
  • 64
  • When I attempt to use SELECTEDVALUE, the expression does not show as valid and it returns an error. When I start to type SELECTEDVALUE, the only things that show up in the AutoComplete are ALLSELECTED and SELECTEDCOLUMNS. – S Grzybowski Nov 14 '17 at 16:15
  • @SGrzybowski You must have an older version (it's a newer function). It's equivalent to `IF(HASONEVALUE(DimDate[Year]), DimDate[Year])`, so you can use that instead. – Alexis Olson Nov 14 '17 at 16:35
  • Is that an older version of Analysis Services on the server or an old version of Visual Studio? I am using SQL Server Data Tools 2015 & the version of SQL Server Analysis Services is 13.0.1742.0 according to the general report when connected to the Analysis Services in SSMS. – S Grzybowski Nov 14 '17 at 17:40
  • My attempt to use the HASONEVALUE function has resulted in an error. `YoY Metric:= VAR CurrentYear = IF(HASONEVALUE(DimDate[Year]), DimDate[Year]) VAR CurrentPeriod = IF(HASONEVALUE(DimDate[Period]), DimDate[Period]) RETURN CALCULATE(SUM(Fact_Transaction[gll_amount]), FILTER(ALL(DimDate), DimDate[Year] = CurrentYear - 1 && DimDate[Period] = CurrentPeriod)) ` – S Grzybowski Nov 14 '17 at 17:46
  • @SGrzybowski It's available in Power BI, but I'm not sure what other programs allow you to use it. I'll add an edit with the equivalent expression. I missed a piece in my comment above. – Alexis Olson Nov 14 '17 at 17:46
  • My Code now looks like: `YoY Metric:= VAR CurrentYear = IF(HASONEVALUE(DimDate[Period]), VALUES(DimDate[Period])) VAR CurrentPeriod = IF(HASONEVALUE(DimDate[Date]), VALUES(DimDate[Date])) RETURN CALCULATE(SUM(Fact_Transaction[gll_amount]), FILTER(ALL(DimDate), DimDate[Year] = CurrentYear - 1 && DimDate[Period] = CurrentPeriod))` However, When using this calculation, the results end up blank. – S Grzybowski Nov 14 '17 at 19:01
  • @SGrzybowski It will show up blank if you don't have any filter context for your dates since `HASONEVALUE` will be false. What is your filter context? Is this a measure or a calculated column? – Alexis Olson Nov 14 '17 at 19:15
  • It is a measure, but even when I apply a context to it in PowerBI, It still has nothing in it. – S Grzybowski Nov 14 '17 at 19:26
  • Ended up needing some minor fixes to your query and ended up with `yoy2 = VAR CurrentYear = IF(HASONEVALUE(DimDate[Year]), VALUES(DimDate[Year])) VAR CurrentPeriod = IF(HASONEVALUE(DimDate[Period]), VALUES(DimDate[Period])) RETURN CALCULATE(SUM(Fact_Transaction[gll_amount]), FILTER(ALL(DimDate), DimDate[Year] = CurrentYear - 1 && DimDate[Period] = CurrentPeriod))` – S Grzybowski Nov 14 '17 at 20:12
  • @SGrzybowski Yes, sorry, that's what I meant to write. Copy and paste errors. It's tough writing formulas without intellisense. Did that work? – Alexis Olson Nov 14 '17 at 20:40
  • Yes it did. Thank You – S Grzybowski Nov 15 '17 at 15:58