Forum Discussion

kagy100's avatar
kagy100
Icon for Advocate II rankAdvocate II
5 years ago
Solved

Dax Assistance Required with Measure

Hi Team,

 

I needed your kind assistance with the below . .. 

The .pbix file can be found here 

 

 

 

Need Help
  • v-alq-msft's avatar
    v-alq-msft
    5 years ago

    Hey, @kagy100

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

    Table A:

    c1.png

    Table B:

    c2.png

    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:

    c3.png

    Best regards

    Allan

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

4 Replies

  • TomMartens's avatar
    TomMartens
    Icon for Super User rankSuper User
    5 years ago

    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 

  • v-alq-msft's avatar
    v-alq-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hey, @kagy100

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

    Table A:

    c1.png

    Table B:

    c2.png

    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:

    c3.png

    Best regards

    Allan

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