Calculate Function not working

Hello,

I’m trying to calculate the number of invoiced items for 10 weeks prior next to each other.

I’m using the following measure for all 10 weeks:

W1 = 
CALCULATE (
    SalesInvoiceLines[Invoiced Quantity],
    DATESBETWEEN (
        SalesInvoiceLines[InvoiceDate],
        MAX ( SalesInvoiceLines[InvoiceDate] ) - 6,
        MAX ( SalesInvoiceLines[InvoiceDate] )
    )
)

The problem is that when i use a dates table linked to invoicedDate it shows blank when linked to Products and releasedProducts tables and if i use the salesInvoiceLines[invoiceDate] the table won’t load at all.

I can’t why both options aren’t working.

Thanks.

PS: same problem if i use FILTER instead of DATESBETWEEN

Hi @rawad_hachem,

Is your date table is marked as “Date” table? Also, it would be really helpful if you attach sample data model.

Regards,
Hafiz

Hi @hafizsultan,

Yes i marked it as date table. unfortunately the data i’m working on is confidential and directly taken from D365 so i can’t send a sample.

I’m trying to achieve a table that has all information of the items along with 10 columns each one of these 10 is 1 week of invoiced items.

Can you share a clear picture of the Model view from your report with all Date table relationships visible?

Hi @Melissa,

here you go. is it clear?

So a clear concern is the numerous bi-directional relationships in your model…
Please refer to this content and see if you can optimize your model:

Furthermore I would also advise you to place the tables more in a waterfall like fashion. That way it becomes much more easy to see how filters move from one table to the next.

2 Likes

Please be sure to check out our DAX Clean Up tool it’s a great way to ensure to make your DAX code easy to read. Thanks!

https://analysthub.enterprisedna.co/dax-clean-up

1 Like