Dynamic Monthly Variance returning zeros

HClegg — Wed, 04 Sep 2024 16:04:40 GMT

I want to see the monthly variance for the products sold. Each month selected displays two measures: sold quantity and variance. The user can select a year dimension and a month dimension, this correctly updates the sales quantity measure.

However, I'm trying to use set analysis to dynamically update the variance of the quantity sold using the below formula - but like I say, it's returning zeros. Please can someone help?

sum({$<Date = {">=$(=Date(MonthStart(Today()), 'DD/MM/YYYY'))<=$(=Date(MonthEnd(Today()), 'DD/MM/YYYY'))"}>} [Sales Quantity])
-
sum({$<Date = {">=$(=Date(MonthStart(AddMonths(Today(), -1)), 'DD/MM/YYYY'))<=$(=Date(MonthEnd(AddMonths(Today(), -1)), 'DD/MM/YYYY'))"}>} [Sales Quantity])

Just to add, I'm aware I can use this:
sum([Sales Quantity])
-
Before(sum([Sales Quantity]))

However, this leaves the first month's variance blank, as there is no preceding month column to calculate a variance from.

Re: Dynamic Monthly Variance returning zeros

Vegar — Wed, 04 Sep 2024 19:29:30 GMT

My best guess is that you have some kind of issue with the Date modifier.

Try removing the date formating like this and replacing it with a num

sum({$<Date = {">=$(=floor(MonthStart(Today())))<=$(=floor(MonthEnd(Today())))"}>} [Sales Quantity])

Or format the date as a string like this:

sum({$<Date = {">='$(=Date(MonthStart(Today()), ''DD/MM/YYYY''))'<='$(=Date(MonthEnd(Today()), ''DD/MM/YYYY''))'"}>} [Sales Quantity])

topic Re: Dynamic Monthly Variance returning zeros in App Development

Dynamic Monthly Variance returning zeros

Re: Dynamic Monthly Variance returning zeros