Filtering Dates from another table

[riledup2021]

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.

[v-dineshya]

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

[lbendlin]

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

[FBergamaschi]

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