Forum Discussion

azeemnazim's avatar
azeemnazim
Helper II
6 years ago
Solved

Sum coming up wrong

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...
  • mahoneypat's avatar
    mahoneypat
    5 years ago

    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

mahoneypat's avatar
mahoneypat
Microsoft Employee
5 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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