Forum Discussion
Data between dates (slider). selection minus 1 year not working
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.
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."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."
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
1 Reply
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