topic Re: Filtering Dates from another table in DAX Commands and Tips

Filtering Dates from another table

riledup2021 — Wed, 30 Jul 2025 17:44:59 GMT

Hello!

So I have been working through attempting to build logic for sales at different grains. r13w, r4w, py r13w, py r4w. Our company has some custom logic for how we define weeks so we have a table put together that shows the week_end_date (calendardt), and each date that would fall into each aggregate for current year(rollupdt)/ Prior year(prioryeardt).

We then have daily level sales data.

I have dax written that doe work to accomplish what I attempted to below:

R1W Sales = CALCULATE(

SUM('Sales Metrics - Weekly'[sales]),

TREATAS(

CALCULATETABLE(VALUES(time_rollup[rollupdt]),time_rollup[timerollupdimcd] = "r1w"),

'Sales Metrics - Weekly'[invoice_dt]

))

The current issue we are facing is with preformance, this is relatively slow.

I was curious if anyone had a better/ more efficient way of grabbing the values from our timerollup table in order to create a similar measure.

Re: Filtering Dates from another table

FBergamaschi — Wed, 30 Jul 2025 18:18:40 GMT

Can you please add the tables in a usable format so we can replicate the measure and improve the performance?

Re: Filtering Dates from another table

lbendlin — Thu, 31 Jul 2025 01:25:10 GMT

If you refresh your calendar table in import mode daily you can implement these ranges/grains as calculated columns.

Re: Filtering Dates from another table

v-dineshya — Thu, 31 Jul 2025 12:06:39 GMT

Hi riledup2021 ,

Thank you for reaching out to the Microsoft Community Forum.

Hi lbendlin , FBergamaschi , Thank you for your prompt responses.

Hi riledup2021 ,

Please try below two options to improve performance.

1. Created Calculated column "IsR1W" to "time_rollup"

IsR1W = IF(time_rollup[timerollupdimcd] = "r1w", TRUE(), FALSE())

Please change you "R1W Sales" measure with below DAX code.

R1W Sales = CALCULATE(
SUM('Sales Metrics - Weekly'[sales]),
FILTER(
'Sales Metrics - Weekly',
'Sales Metrics - Weekly'[invoicedt] IN
CALCULATETABLE(
VALUES(time_rollup[rollupdt]),
time_rollup[IsR1W] = TRUE()
)
)
)

2. Create a date bridge table from "time_rollup", only include rows with "r1w" and their "rollupdt". Make relationship "rollupdt" --> "invoiced" directly in the model.

Use the time filter from Time_R1W

R1W Sales = SUM('Sales Metrics - Weekly'[sales])

I hope this information helps. Please do let us know if you have any further queries.

Regards,

Dinesh

Re: Filtering Dates from another table

v-dineshya — Mon, 04 Aug 2025 17:29:49 GMT

Hi riledup2021 ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.

Regards,

Dinesh

Re: Filtering Dates from another table

v-dineshya — Fri, 08 Aug 2025 15:26:21 GMT

Hi @riledup2021 ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.

Regards,

Dinesh

Re: Filtering Dates from another table

v-dineshya — Mon, 11 Aug 2025 16:05:05 GMT

Hi @riledup2021 ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.

Regards,

Dinesh