Calculate Function not working

DAX DAX Calculations
1

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

2

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

3

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.

4

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

5

Capture
Capture1793×1265 126 KB

Hi @Melissa,

here you go. is it clear?

6

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
7

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