Dax Assistance Required with Measure

[kagy100]

Hi Team,

I needed your kind assistance with the below . .. 

The .pbix file can be found here 

[v-alq-msft · Accepted solution]

Hey, @kagy100

According to your description, I created data to reproduce your scenario. The pbix file is attached at the end.

Table A:

Table B:

There's a relationship between tables. You can create measures as follows.

Start Month = 
FORMAT(
    CALCULATE(
        MAX('Table A'[Date]),
        FILTER(
            ALL('Table A'),
            [Date]<=MAX('Table B'[Motor Enhancement Date])
        )
    ),
    "mmm-yy"
)

End Month = 
FORMAT(
    CALCULATE(
        MIN('Table A'[Date]),
        FILTER(
            ALL('Table A'),
            [Date]>MAX('Table B'[Motor Enhancement Date])
        )
    ),
    "mmm-yy"
)

Start Month Value = 
var startdate = 
CALCULATE(
        MAX('Table A'[Date]),
        FILTER(
            ALL('Table A'),
            [Date]<=MAX('Table B'[Motor Enhancement Date])
        )
)
return
CALCULATE(
    SUM('Table A'[Motor Value]),
    FILTER(
        ALL('Table A'),
        [Date]<=startdate
    )
)

End Month Value = 
var enddate = 
CALCULATE(
        MIN('Table A'[Date]),
        FILTER(
            ALL('Table A'),
            [Date]>MAX('Table B'[Motor Enhancement Date])
        )
)
return
CALCULATE(
    SUM('Table A'[Motor Value]),
    FILTER(
        ALL('Table A'),
        [Date]>=enddate
    )
)

Result:

Best regards

Allan

If this post helps, then consider Accepting it as the solution to help other members find it faster.

[kagy100]

Thank You @v-alq-msft for the kind assistance. 🙂

[TomMartens]

Hey @kagy100 ,

please consider creating a pbix file that contains the sample data from above. Upload the pbix to onedrive or dropbox and share the link.

I'm not sure what you have, meaning what data is available and what data needs to be calculated. I'm also wondering if a relationship between both tables can be established.

Regards,

Tom 

[kagy100]

Thanks for replying Tom,

The .pbix file can be found here: Usage Example