Count the postive and negative values from this year to last year sales comparison values

[san_jois]

Hi,

I have a Daily data table with 'date', 'Customer name'and 'sales quantity' columns. Data gets added on daily basis into the model

Based on the slicer of year, month or week selected, I want to compare this year sales with the last year sales and get:

i) Increase in sales quantity-No change-Or decrease in sales quanitity

ii) Count no. of Customers for whom Sales has increased-no change-or decreased

I have created following measures: Total sales and Total sales LY (using sameperiodlastyear DAX)

With these in place, Sl (i) above is solved

However, based on increase or decrease in sales, because I have to distinctly count Customer namewise i.e. rowwise, the above measures are not helping.

I am trying to add new column in Daily data table which has last year sales quantities. And another column which will have difference in sales this year to sales last year columns. I am getting circular reference error for the latter column.

Is this approach correct? 

What is the DAX for the said new columns?

Thanks in advance...

[Ashish_Mathur]

Hi,

Share your file/data here and show the exact result you are expecting.

[san_jois]

Hi Ashish,

Thanks for replying

I have created the following measures which get populated in the table as indicated:

Sample table created using Measures

In the data, I have one column called Quantity.

I have created measures which I have added in the table visual above as columns...

Basically, i want to count the no. of negatives and no. of positives in the column of the table-visual above under the measure Monthly sales difference and Yearly sales difference.

I know a simple measure won't work as we are referencing individual rows and counting them based on a condition

Adding a column in the complete data table which is running into lakhs of rows would be not a smart move as it consumes ram..

If inevitable, I have to add. 

So the help I need is :

i) is there a way i can prevent not to add a calculated column?

ii) If i have to add, what would be the DAX

Regards,

[Ashish_Mathur]

Hi,

Share the link from where i can download your file.

[san_jois]

HI,

I have a Date and Sales Quantity columns in which data gets added daily.

Based on the slicer chosen, I want to compare this year sales with that of last year and display:

i) Positive or zero or negative difference in sales quantity

ii) Count the no. of customers falling in each category.

The Sl i) above is solved using measures. I am stuck in Sl (ii) as I cannot successfully compare rowwise data using measures.

Am trying to create a column..not sure if this is right thing to do..

Thanks in advance

Regards,