DAX: Filter by date in another Table

[DDL76]

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!!!!

[DDL76]

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

[Anonymous]

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. 

[DDL76]

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

[Anonymous · Accepted solution]

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.