topic Re: DAX: Filter by date in another Table in DAX Commands and Tips

DAX: Filter by date in another Table

DDL76 — Thu, 11 Jul 2024 19:40:00 GMT

Hi there,

I curently have this DAX formula that works. 🙂

FC = Calculate(sumx(Transactions,Transactions[Amount] *related(Codes[Natural Value])) ,FILTER(Transactions,Transactions[Scenario]="Actuals"))

However i need to limit the data returned further by the date...

The Transactions[Date] must be equal to or less than the "Last Close" date in the table below called "DATES".

DATE TYPE	DATE
Last Close	30/06/2024

Many Thanks in advance for your assistance!!!!

Re: DAX: Filter by date in another Table

Anonymous — Fri, 12 Jul 2024 02:08:01 GMT

Hi DDL76 ,

Please try this:

FC = var _last_date=CALCULATE(MAX('Transactions'[DATE]),FILTER(ALLSELECTED('Transactions'),'Transactions'[DATE TYPE]="Last Close")) return Calculate(sumx(Transactions,Transactions[Amount] *related(Codes[Natural Value])) ,FILTER(Transactions,Transactions[Scenario]="Actuals"&&Transactions[Date]<=_last_date ))

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly.

Re: DAX: Filter by date in another Table

DDL76 — Fri, 12 Jul 2024 09:21:37 GMT

Hi there,
Many thanks for your help, I think we are very close! I think the problem is the data in the table "Transactions" needs to be limited to based on a date held in another table called "Dates".

So Transactions[Date] must be = to or less than DATES[DATE] where DATES[DATE TYPE] = 'Last Close'.
It might be worth noting that there is no relationsip between these tables, not sure if this causes an issue? I have included images of the tables and the error shown in the DAX formula. Many thanks again!!!

PREVIEW

Re: DAX: Filter by date in another Table

Anonymous — Mon, 15 Jul 2024 02:14:36 GMT

Hi DDL76 ,

Please update it.

FC = var _last_date=CALCULATE(MAX('DATES'[DATE]),FILTER(ALLSELECTED('DATES'),'DATES'[DATE TYPE]="Last Close")) return Calculate(sumx(Transactions,Transactions[Amount] *related(Codes[Natural Value])) ,FILTER(Transactions,Transactions[Scenario]="Actuals"&&Transactions[Date]<=_last_date ))

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly.

Re: DAX: Filter by date in another Table

DDL76 — Mon, 15 Jul 2024 11:44:49 GMT

Neeko Tang, Many thanks its all working as intended! 🙂