Power BI with direct query data source - DAX measure to get previous year's value not working

[009co]

I have what should be fairly straightforward calculation but it is not working.

I have SQL Server database datasource that I am retrieving using direct query.

I have a related date table.

In Power BI, I have a measure:

ACT = CALCULATE( SUM('OLAP CUBE_Finance_WB'[Amount]), 'OLAP Scenario'[Name]="Actual" )

I am trying to get the previous year's value using following methods. I understand that SAMEPERIODLASTYEAR may not work with direct query datasource, so i have tried all three methods below:

ACT PY = CALCULATE([ACT],DATEADD('OLAP Month'[Date],-1,YEAR))

ACT PY = CALCULATE([ACT],PARALLELPERIOD('OLAP Month'[Date],-1,YEAR))

ACT PY = CALCULATE([ACT],SAMEPERIODLASTYEAR('OLAP Month'[Date])

However, while these don't return an error, nothing is populated in the new measure column eg the ACT PY measure doesn't return any values.

The 'OLAP Month' date table [Date] value is proper date. For example, in the 'OLAP Month' table I am able to create a previous year date successfully using:

DatePY = DATEADD('OLAP Month'[Date], -1, YEAR)

The [ACT] metric is calculated as follows:

ACT = CALCULATE(SUM('OLAP CUBE_Finance_WB'[Amount]),'OLAP Scenario'[Name]="Actual")

I have another measure that uses the [ACT] measure that is working fine so my issue doesn't seem related to that:

ACT YTD = CALCULATE([ACT], all('OLAP Month'), DATESYTD('OLAP Month'[Date]))

Any hints, tips, ideas about why my previous year ACT value is not being calculated?

[009co]

Ok, including all('OLAP Month') did actually make it work.

I had assumed that the all('OLAP Month')  was part of summing year to date values. 

What is the logic / reason for including this all('OLAP Month')  in the ACT PY calculation?

Whenever I have seen any previous period DAX examples, they are always like CALCULATE([ACT],DATEADD('OLAP Month'[Date],-1,YEAR)) without anything like the all('OLAP Month').

[AlexisOlson]

The ALL function removes filters on the table it applies to. If the only filters were on the 'OLAP Month'[Date] column, this shouldn't be required since CALCULATE replaces filter context for the columns specified. This is why you don't often see it in the DAX examples.

However, if you have filters on other columns in your 'OLAP Month' table, then these filters get combined with the new updated filter on 'OLAP Month'[Date], likely resulting in an empty set of dates. I suspect this is what was happening in your case, although it's difficult to verify without actually seeing your file.

[009co]

Interesting. There are filters on the table data but they are things that remove rows but wouldn't filter out relevant dates.

Filter 1: The dataset has both ACTUAL and BUDGET rows, one filter retained only ACTUAL rows.

Filter 2: The dataset has both PL and BS rows, the other filter retained only PL rows.

The retained rows do have all dates in the range of the  'OLAP Month' table. But clearly the ALL function works to get full set of dates. So perhaps some of the dates get filtered out. 

[AlexisOlson · Accepted solution]

Your [ACT YTD] measure includes all('OLAP Month') as an argument whereas your PY attempts do not. Does it help if you include all('OLAP Month') in those too? You might have some filter context that is not being replaced.