Data between dates (slider). selection minus 1 year not working

[Anonymous]

Hi, 

I am trying to make a comparison of Revenue between the selected period (slider) and the same period last year. I have tried this in a couple difrent ways now, but i seem to keep getting errors. I made a difrend table so that there can be no filters interfering with the data. This is the code i am trying at the moment:

OmzetVorigJaar = 
var minDate = CALCULATE(MIN(revArticleRevenue[Factuurdatum]), ALLSELECTED(revArticleRevenue[Factuurdatum]))
var maxDate = CALCULATE(MAX(revArticleRevenue[Factuurdatum]), ALLSELECTED(revArticleRevenue[Factuurdatum]))

var prefYear = YEAR(minDate)-1
var prefMinDate = DATE(prefYear , MONTH(minDate), DAY(minDate))
var prefMaxDate = DATE(prefYear , MONTH(maxDate), DAY(maxDate))

var datesBetweenPref = DATESBETWEEN(cvDailyRevenue[Factuurdatum], prefMinDate, prefMaxDate)

var revenue = CALCULATE(
   SUM('cvDailyRevenue'[DagOmzet]),
   FILTER('cvDailyRevenue', 'cvDailyRevenue'[Factuurjaar] = prefYear),
   FILTER('cvDailyRevenue', 'cvDailyRevenue'[Factuurdatum] in datesBetweenPref)
)

RETURN revenue

 at first i did not use prefYear but Year(minDate) - 1 this seemed to not always work.

If i use 2 static dates in the between like : 

var datesBetweenPref = DATESBETWEEN(cvDailyRevenue[Factuurdatum], 01-01-2018, 31-12-2018)

it works fine. but this does ofc not let me compare periods set by the slider.

I Also tried it this way:

var revenue = CALCULATE(
   SUM('cvDailyRevenue'[DagOmzet]),
   FILTER('cvDailyRevenue', 'cvDailyRevenue'[Factuurjaar] = prefYear),
   FILTER('cvDailyRevenue', 'cvDailyRevenue'[Factuurdatum] >= prefMinDate),
   FILTER('cvDailyRevenue', 'cvDailyRevenue'[Factuurdatum] <= prefMaxDate)
)

this again returns an error, but with the static dates it works.

The error i am getting with this is:

"MdxScript(Model) (610, 19) Calculation error in measure 'revArticleRevenue'[OmzetVorigJaar]: An argument of function 'DATE' has the wrong data type or the result is too large or too small."

[Anonymous · Accepted solution]

i found that Power Bi has a harder job working with Dates etc. therfore i made it so that powerapps can work with ints. In the view i returned the day of the year and the year number as an int. Then the following changes to the code where made: 

OmzetVorigJaar = 
var minDate = CALCULATE(MIN(revArticleRevenue[Factuurdatum]), ALLSELECTED(revArticleRevenue[Factuurdatum]))
var maxDate = CALCULATE(MAX(revArticleRevenue[Factuurdatum]), ALLSELECTED(revArticleRevenue[Factuurdatum]))

var dayOfYearMin = DATEDIFF(DATE(YEAR(minDate), 1, 1), minDate, DAY ) + 1
var dayOfYearMax = DATEDIFF(DATE(YEAR(minDate), 1, 1), maxDate, DAY ) + 1


var revenue = CALCULATE(
   SUM('cvDailyRevenue'[DagOmzet]),
   FILTER('cvDailyRevenue', 'cvDailyRevenue'[Factuurjaar] = YEAR(minDate)-1),
   FILTER('cvDailyRevenue', 'cvDailyRevenue'[Factuurdag] >= dayOfYearMin),
   FILTER('cvDailyRevenue', 'cvDailyRevenue'[Factuurdag] <= dayOfYearMax)
)

RETURN revenue