If statement with condition from two non-related tables

[Simkia]

Hello!

I would think that this would be rather simple, but I'm scratching my head a lot on this one.

I would like to make an overview of how many active orders I have on any given date, in order for an order to be active I want it to say:

date order is received <= max date

&& date order is completed > max date.

I have a dimCalendar table and I have another table with some order received dates (Tilgået Doc.) and orders completed dates (Udført Doc. Date) in it.

Orders table:

 
Date table:

     

I then figured that I wanted to make some sort of column in the DimCalendar table that counts how many orders I have received on every date, but to make this column without an active relationship between the two tables.

The reason for this is that, I figured I would put it into this formula, where it says "Datokolonne", and I would expect to be able to see how many active orders I have on any given date. 

Column 1 = 
    CALCULATE(
        SUM(Datokolonne),
        filter(
            dimCalendar,
            dimCalendar[Date] >= max('AX Data'[Tilgået Doc.]) 
            && dimCalendar[Date] < max('AX Data'[Udført Doc. Date])))

Any other solutions to my issue would also be greatly appreciated! 

Thanks in advance!

[StefanoGrimaldi]

you can add to that dax a relatnionship function: https://docs.microsoft.com/en-us/dax/userelationship-function-dax to use a inactive relantioship between the tables that evaluates using that relantionship only for that dax formula context, add it as a filter of the calculate function. 

[Simkia]

I'm not entirely sure how to write the column code, that is supposed to count how many orders I have received on every date. I figured it would be something similar to 

Column 2 = 
    if('AX Data'[Tilgået Doc]=dimCalendar[Date],+1,0)

However it does not allow me to do this, even if I add the USERELATIONSHIP. 

How would you make a column in my Calendar table that adds up orders received on every individual date, whilst not having an active relationship?

[StefanoGrimaldi · Accepted solution]

you add at the end as a argument of the calculate dax the function userrelantionship

Column 1 = 
    CALCULATE(
        SUM(Datokolonne),
        filter(
            dimCalendar,
            dimCalendar[Date] >= max('AX Data'[Tilgået Doc.]) 
            && dimCalendar[Date] < max('AX Data'[Udført Doc. Date])))