The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now
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."
Solved! Go to 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
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
User | Count |
---|---|
17 | |
2 | |
2 | |
2 | |
1 |
User | Count |
---|---|
20 | |
10 | |
5 | |
4 | |
3 |