Sum coming up wrong

[azeemnazim]

Hi,

I am using a date slicer on a stacked column chart but with item code and Net total amount, but on the date between 31/1/2019 the values are coming wrong for item code A and D. It is to be noted that these two have negative values of the net amount in them I have tried the below measures but they were of no help.

Measure = CALCULATE(SUM('ITEM Table'[Net Amount]),FILTER('ITEM Table','ITEM Table'[Item Code]=MAX('ITEM Table'[Item Code])))

and 

Measure 2 = IF(HASONEFILTER('Calender Table'[Date]),SUM('ITEM Table'[Net Amount]),SUMX(FILTER('ITEM Table','ITEM Table'[Item Code]=MAX('ITEM Table'[Item Code])),'ITEM Table'[Net Amount]))

according to me the value of item code D when the date set in the slicer is 31/01/2019 should be (-1,29,993) but it is coming something else. Below is the link to my pbix file.

https://drive.google.com/file/d/1IKWatJL50qZ4VKpnQjdn5kiOhoMhM_4a/view?usp=sharing 

and below is the link to the excel data source and question.

https://drive.google.com/file/d/1L2KEnjqESxnEuOhtY13GFz9UKOGc_GCy/view?usp=sharing 

[mahoneypat]

I looked at your pbix.  FYI that a simpler can be used in your model to get the same result

Measure 3 = SUM('ITEM Table'[Net Amount])

Also, when the slice is set to just Jan 2019, the result for item D is -2.6M which seems correct as there are only two rows for item D in Jan 2019 (-3.6M and 1.0M).

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[azeemnazim]

@mahoneypat Thanks for the solution, can please assist with the measure to plot another graph summarizing all transactions due on or before 31/01/19 basis Due Date Column

[mahoneypat · Accepted solution]

I'm not sure exactly what you're looking for, so two options below.

If you really want a measure with a fixed date filter, you can use this.

Fixed sum due date ?
CALCULATE (
SUM ( 'Item Table'[Net Amount] ),
ALL ( 'Calender Table'[Date] ),
'ITEM Table'[Expiration Date] <- DATE ( 2019, 1, 31 )
)

If you want to use the values in the date segmentation, but in the Due date column instead, you can use this one.

Add SlicerDates expiration date ?
CALCULATE (
SUM ( 'Item Table'[Net Amount] ),
ALL ( 'Calender Table'[Date] ),
TREATAS ( VALUES ( 'Calender Table'[Date] ), 'Item Table'[Expiration Date] )
)

In either, if you do not want to remove the filter from the Date column of the calendar[Date], remove the ALL() part.

If this works for you, mark it as the solution. Praise is also appreciated. Please let me know if you don't.

Best regards

Pat

[azeemnazim]

@mahoneypat

Many thanks !!

[Anonymous]

Hi @azeemnazim ,

Whether your problem has been resolved? If yes, could you please mark the helpful post as Answered? It will help other members in the community find the solution easily if they face the similar problem with you. Thank you.

Best Regards

Rena

[Fowmy]

@azeemnazim 

Not sure about what you are trying to calculate here. However, I hope you select dates up to 31/01/2020. but you have values for D after that particular date.

Please explain more clearly.

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube, LinkedIn

[azeemnazim]

Hi,

The values in the visualization are not coming up correctly due to negative value i guess, Moreover, I have attached an excel file for data and my pbix file for a complete analysis

Thanks.

[azeemnazim]

@Fowmy 

 The issue due to negative value i guess , Moreover i have attached the pbix file and excel the values are not coming up correctly for item code A and D

[Fowmy]

@azeemnazim 

I checked the file but cannot understand your question. Please provide an example and explain how you are trying to calculate the value for the letter "D" for that amount. Can you show me the calculation logic?

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube, LinkedIn