DAX help needed to calculate a value for the days in selected month when they fall between two dates

[grazzhopper]

Hi Ladies en Gents,

Probably should know this, but I cannot figure out my DAX for the following:

I have a large table [InOutRecords] with unique ID-records with an INdate and an OutDate and a calculated column that displays the difference between the INdate and the OUTdate. Nothing fancy.

 

I also have a [DatesTable] where every date has a Value and a Monthly period per year, how many days are in that month and the start and enddate on that month. It runs every date from 1-1-2018 to 31-12-2025

When I select the period 2018-M10 in the Filter for the [DatesTable]} I would like the sum of the Value for the days that the records are within that timespan.
 

So when selected the Monthly Period of 2018-10:

a record with INdate 15-09-2018 and OUTdate 24-09-2018 should result in 0*Value

a record with INdate 15-09-2018 and OUTdate 2-10-2018 should result in 2*Value

a record with INdate 2-10-2018 and OUTdate 5-10-2018 should result in 3*Value

a record with INdate 23-09-2018 and OUTdate 12-11-2018 should result in 31*Value

a record with INdate 28-10-2018 and OUTdate 10-11-2018 should result in 4*Value

Anyone has a pointer on how to tackle this in DAX?

Thnx in advance!

Grazz

[v-yuta-msft]

Hi grazzhopper, 

Have you solved your issue by now? If you have, could you please mark one correct one to finish the thread?

Regards,

Jimmy Tao

[grazzhopper]

There is no relation between de tables 'inventory' and 'reportdates'. I just want to select the values from Inventory where the OUTdates are within the selected period or empty.

Weird thing (for me) is, when I use a fixed date I get the right values returned (see Result 1). However, when I replace the fixed dates with a MAX(reportdates[Date]) and a MIN(reportdates[Date]) I get the results from the MIN and MAX from all the values of the Reportdates table, and not the MIN and MAX from the sliced selector; eventhough the MIN and MAX values are displaying the expected dates when I display them onscreen in a datacard.

So I think the table is calculated first and after that slicers are applied?

Result 1

TestTabel = CALCULATETABLE('inventory'; 
    FILTER('inventory';
             ISBLANK('inventory'[OUTdate]) ||
            'inventory'[OUTdate] >= DATE(2018;10;1) &&
            'inventory'[OUTdate] <= DATE(2018;10;31)    
    )
)

 

Result 2:

TestTabel = CALCULATETABLE('inventory'; 
    FILTER('inventory';
            ISBLANK('inventory'[OUTdate]) ||
            'inventory'[OUTdate] >= MIN(reportdates[Date]) &&
            'inventory'[OUTdate] <= MAX(reportdates[Date])
    )
)

 

[v-yuta-msft]

@grazzhopper, 

Modify your measure with formula below and check if the result is based on the values you selected in the slicer.

TestTabel =
CALCULATETABLE (
    'inventory';
    FILTER (
        'inventory';
        ISBLANK ( 'inventory'[OUTdate] )
            || 'inventory'[OUTdate]
                >= CALCULATE ( MIN ( reportdates[Date] ); ALLSELECTED ( reportdates ) )
                && 'inventory'[OUTdate]
                    <= CALCULATE ( MAX ( reportdates[Date] ); ALLSELECTED ( reportdates ) )
    )
)

Regards,

Jimmy Tao

[grazzhopper · Accepted solution]

Hi Jimmy, tried it, but didn't work either.

The solution I eventually settled for is a combination in what this thread learned me and what I could come up with.

Added an apocalypse-day (=end of the reportdates) to replace empty OUTdates) with M and used a crossjoin on the two tables to get the right result.

CalculatedTable =
SELECTCOLUMNS (
    FILTER (
        CROSSJOIN ( 'inventory'; reportdates);
            'inventory'[INdate] <= reportdates[Date]
            && 'inventory'[OUTdates] >= reportdates[Date]
        );
    "id"; 'inventory'[id];
    "Date"; reportdates[Date];
    "Amount"; reportdates[StoragePricePerDay];
    "Warehouse"; 'inventory'[warehousenr]
)

I could have used and extra IF statement to catch all empty OUTdates, but I replaced the apocalypse day with a label "STOCK", so I can show the stock also within the graphs on the same time with the movements.

Perhaps not the most elegant way, but hey, you can slice cheese with a breadknife too. 😉 

Thnx for all the help guys!

Best regards,

Grazz.

[v-yuta-msft]

@grazzhopper,

Great to hear that. Could you please help mark the correct answer to finish the thread? Your contribution will be much appreciated.

Regards,

Jimmy Tao

[grazzhopper]

Hi Jimmy,

Sadly no, I haven't solved it yet. 😞

[v-yuta-msft]

Hi grazzhopper,

To be general,  suppose table InOutRecords and table DatesTable have relationship based on column id, you can merge the two tables based on the id column, then you may create measure formula below in Merge table and check if it can meet your requirement:

Result =
CALCULATE (
    Merge[Value],
    FILTER (
        Merge,
        Merge[Monthly Period of Year] = SELECTEDVALUE ( Merge[Monthly Period of Year] )
            && Merge[INdate] >= Merge[MonthStart]
            && Merge[OUtdate] <= Merge[MonthEnd]
    )
)

Regards,

Jimmy Tao

[GilbertQ]

Hi @grazzhopper

Firstly make sure that there is no relationship between your tables.

This is because what you are wanting to do, is to have a measure which is between 2 dates. If you have a relationship it can only be on on value, which will filter to that particular value.

Below is a measure that I have successfully used in the past. Just change it to your table and column names

Active Users =  
CALCULATE (  
    DISTINCTCOUNT ( Table[UserID] ),  
    FILTER (  
        'Table',  
        'Table'[Start Draw] <= MAX ( 'Draw'[Draw] )  
            && 'Table'[End Draw] >= MIN ( 'Draw'[Draw] )  
    )  
)