topic Re: How to calculate actual sales with using dax language instead of in excel? in DAX Commands and Tips

How to calculate actual sales with using dax language instead of in excel?

ommfin123 — Tue, 18 Jul 2023 07:59:50 GMT

Hi all. Wish you a good day!

I would like to add a column to calculate the total of actual sales in my Target table. (by month & year as the Target table)

Current formula I am using as below:

Actual Sales = CALCULATE(SUM('Sales Report'[Total]),

FILTER('Sales Report', 'Sales Report'[Estimated Delivery Date]='SalesPlanning'[DATES]))

But what I want is date according to the table.

The results I got:

Appreciates lot for your help!

Re: How to calculate actual sales with using dax language instead of in excel?

tamerj1 — Tue, 18 Jul 2023 11:17:04 GMT

Hi ommfin123
Please try

Actual Sales = CALCULATE ( SUM ( 'Sales Report'[Total] ), FILTER ( ALL ( 'Sales Report' ), 'Sales Report'[Estimated Delivery Date] = RELATED ( 'SalesPlanning'[DATES] ) ) )

Re: How to calculate actual sales with using dax language instead of in excel?

ommfin123 — Thu, 20 Jul 2023 03:19:16 GMT

Dear tamerj1 . Thank you for your help!

The formula is with no error but the results comes out were the total sales amount of the whole table.

I would like it to filter according to the SalesPlanning's monthyear date, may I know how should I do?

Thank you so much.

Re: How to calculate actual sales with using dax language instead of in excel?

tamerj1 — Thu, 20 Jul 2023 09:50:44 GMT

ommfin123

Please try

Actual Sales =
CALCULATE (
SUM ( 'Sales Report'[Total] ),
FILTER (
CALCULATETABLE ( 'Sales Report', ALL ( 'Sales Report' ) ),
'Sales Report'[Estimated Delivery Date] = RELATED ( 'SalesPlanning'[DATES] )
)
)

Re: How to calculate actual sales with using dax language instead of in excel?

ommfin123 — Fri, 21 Jul 2023 00:53:02 GMT

Hi tamerj1 . Thank you for your warm help.

Unfortunatelt it still can't get it :'(

Re: How to calculate actual sales with using dax language instead of in excel?

ommfin123 — Fri, 21 Jul 2023 01:02:22 GMT

Or maybe is my tables' relationship linked wrongly?

I'm not sure too.