Hi, I am looking for someone helping me with an issue. I need to calculate the percentage difference from the previous parallel period (same month in the previous year) and from the previous period (previous month) for an aggregate measure that is calculated as following: Distinct [_ByGroup_] ('ID'n) ID is the identification code for unique client. And I also need this all to be parametrized. To sum up, right now in the report, I can choose a date (that is collected in the parameter Selected_date) and for that date I can see: The count from the beginning of the current month up to Selected_date The count from the beginning of the same month in the previous year (up to the day of the month defined in Selected_date) The count from the beginning of the previous month and up to the day of the month defined in Selected_date What I need to find and show is the percentage difference for both cases. Let’s focus on the difference from the previous month. Concerning to the % difference from the previous month, right now we have an aggregate measure calculated as follow: ( RelativePeriod(_DistinctCount_, 'ID'n, _ApplyAllFilters_, 'Order_date'n, _ByMonth_, 0, _Full_, {Date}) / RelativePeriod(_DistinctCount_, 'ID'n, _ApplyAllFilters_, 'Order_date'n, _ByMonth_, -1, _Full_, {Date}) ) - 1 Order_date is the date we need to take into consideration when counting, the date in which the order has taken place. And we also are applying the following filter directly in the report object: IF ( 'Selected Measure'p = 'MTD' ) RETURN ( IF ( Month('Selected_date'p) = 1 ) RETURN ( ( ( 'Order_date'n >= DateFromMDY(12, 1, ( Year('Selected_date'p) - 1 )) ) AND ( 'Order_date'n <= DateFromMDY(12, DayOfMonth('Selected_date'p), ( Year('Selected_date'p) - 1 )) ) ) OR ( ( 'Order_date'n >= DateFromMDY(1, 1, Year('Selected_date'p)) ) AND ( 'Order_date'n <= DateFromMDY(1, DayOfMonth('Selected_date'p), Year('Selected_date'p)) ) ) ) ELSE ( ( ( 'Order_date'n >= DateFromMDY(( Month('Selected_date'p) - 1 ), 1, Year('Selected_date'p)) ) AND ( 'Order_date'n <= DateFromMDY(( Month('Selected_date'p) - 1 ), DayOfMonth('Selected_date'p), Year('Selected_date'p)) ) ) OR ( ( 'Order_date'n >= DateFromMDY(Month('Selected_date'p), 1, Year('Selected_date'p)) ) AND ( 'Order_date'n <= DateFromMDY(Month('Selected_date'p), DayOfMonth('Selected_date'p), Year('Selected_date'p)) ) ) ) ) ELSE ( 'Order_date'n > DateFromMDY(1, 1, 2999) ) But this measure is working only for the current month and it returns 100% if we select a date in March (month previous to the current one) and “missing” if we select a date in a month previous to March. Is there anyone who can help me in fixing it? Thank you a lot and I am at disposal for any clarification! Regards, Giulia
... View more