DAX code to aggregate values of time stamps in one fact table based on time window in another table

[InsHunter]

Hi,

Being a newbie struck in code for using two fact tables.

Have two fact tables and one dim table linked with common column Meter_ID.

Objective - Aggregate values against each time stamp of one fact table based on the date and time window(Between start time and end time)  in another fact table for each Meter_ID. 

"tm_periodflow_instants" table has Time stamp and Dates separately along with the values which need to be aggregated based on "Date" ,"Start Time" ,"End Time" of another fact table "Prod_data_from_software".
I could not complete the DAX formula for the measure  which i started as below as related was not bringing the other fact table columns for filtering.

Energy Values =

CALCULATE(

    SUMX(tm_periodflow_instants,tm_periodflow_instants[Value]),

     FILTER(ALL(tm_periodflow_instants),tm_periodflow_instants[Time] < RELATED(P

)))

Deeply appreciate your support to complete the code 
(For some reasons could not upload the data model  images in the forum after repeated attempts)

[tamerj1 · Accepted solution]

Hi @InsHunter 
Please try

Energy Values =
SUMX (
    Prod_data_from_software,
    VAR CurrentDate = Prod_data_from_software[Date]
    VAR InstantsTable =
        CALCULATETABLE (
            tm_periodflow_instants,
            CROSSFILTER ( Prod_data_from_software[Meter_ID], 'Meter List'[Meter_ID], BOTH )
        )
    VAR FilteredInstantsTable =
        FILTER (
            InstantsTable,
            tm_periodflow_instants[Date] = CurrentDate
                && tm_periodflow_instants[Time] <= Prod_data_from_software[End Time]
                && tm_periodflow_instants[Time] >= Prod_data_from_software[Start Time]
        )
    RETURN
        SUMX ( FilteredInstantsTable, tm_periodflow_instants[Value] )
)

[InsHunter]

@tamerj1  Thanks . That was an immense help for me.God bless you. I think i need the date level granularity in both "Start Time" and "End Time" in my "Prod_data_from_software" table to get more accurate values when a batch on a machine is transcending the midnight. Hope in that case i need to remove the stand alone date in the filter of variable "FilterInstantsTable"  and bring it to the time .right?

[tamerj1]

@InsHunter 

Yes. On this case DateTime shall be required in both tables (the three Time columns)

[InsHunter]

@tamerj1  Hi.Sorry to bother u again. Since my "Prod_data_from_software" table  had the time granularity in secs i rouned down to minutes to match with my "tm_periodflow_instants" table which is having the time stamp in minutes accepting some  compromise. As date in  "Prod_data_from_software" is end date ,start date was derived based on the time stamps and minutes field for dax purpose. I modified dax as below.

Energy Values =

SUMX (

    Prod_data_from_software,

    VAR StartDate = Prod_data_from_software[Start Date]

    VAR EndDate = Prod_data_from_software[End Date]

    VAR InstantsTable =

        CALCULATETABLE (

            tm_periodflow_instants,

            CROSSFILTER ( Prod_data_from_software[Meter_ID], 'Meter List'[Meter_ID], BOTH )

        )

    VAR FilteredInstantsTable =

        FILTER (

            InstantsTable,

            tm_periodflow_instants[Date] <= EndDate

                && tm_periodflow_instants[Time] <= Prod_data_from_software[Rounded End Time]

                && tm_periodflow_instants[Time] >= Prod_data_from_software[Rounded Start Time]

            && tm_periodflow_instants[Date]>=StartDate

        )

    RETURN

        SUMX ( FilteredInstantsTable, tm_periodflow_instants[Value] )

)

.But unfortunately not getting the values of batches crossing midnight.

Your help will be of great support for me. Thanks in Advance.

[tamerj1]

@InsHunter 

Please try

VAR FilteredInstantsTable =
    FILTER (
        InstantsTable,
        tm_periodflow_instants[Time] <= Prod_data_from_software[Rounded End Time]
            && tm_periodflow_instants[Time] >= Prod_data_from_software[Rounded Start Time]
    )

[InsHunter]

@tamerj1  Thank you very much. I combined date and time in both data sets and used for the filter with start date as initial filter .Then it worked.It was a great help.

[InsHunter]

@tamerj1  Thank you very much.😀

[InsHunter]

Each machine has the below data of times along with dates batch wise.

Value need to be aggregated based on the respective time stamps falling in the above window.