topic Re: Column A Sum based on Column B Dates in DAX Commands and Tips

Column A Sum based on Column B Dates

Napes001 — Wed, 08 Nov 2023 05:12:04 GMT

Im trying to get the measure sum of a column from a table by the dates of the second column and display as sum which is then filtered by the date calander.

Example:

Ordered Amount	Delivery Date
$100
$150	01-01-2023
$75	15-02-2023
$20
$25
$100	20-01-2023

The return should be $325, Which then should be able to be filtered down further by a slicer date table.

thanks

Re: Column A Sum based on Column B Dates

FreemanZ — Wed, 08 Nov 2023 05:44:39 GMT

hi Napes001 ,

could you say more about your expectation, ideally with expected result table?

Re: Column A Sum based on Column B Dates

Napes001 — Wed, 08 Nov 2023 07:07:54 GMT

The table columns are similar to the above table with blank dates. The expected result would be: SUM of "Ordered Amount" with dates.

So based on the table on the original post, My expectation is that the result would be $325 - only sum of Ordered Amoiunt that has delivery dates.

This would then be able to be filtered with the Date calander. IE: Filter by Jan 2023 = $250, Feb = $75 = March = $0.00, etc

I hope this makes sense?

Re: Column A Sum based on Column B Dates

FreemanZ — Wed, 08 Nov 2023 11:11:46 GMT

hi Napes001 ,

try to plot the month column with a measure like:

measure = CALCULATE( SUM(data[Ordered Amount]), data[Delivery Date]<>BLANK() )

it worked like:

Re: Column A Sum based on Column B Dates

Napes001 — Wed, 08 Nov 2023 22:57:26 GMT

Thanks FreemanZ, This appears to be working to an extent. But when I try and filter with my Calander table it wont show anything past 'todays' data?

If I dont filter, it shows the total correctly of past, present and future. I have added a image to hopefully clarify. I appreacitate your help, This is all a new learning

Re: Column A Sum based on Column B Dates

Napes001 — Fri, 10 Nov 2023 03:01:33 GMT

I figured out the issue, I had an inactive date relationship. Added in the USERELATIONSHIP and it now works correctly. thank you for your help.