Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

DAX Help

I do have a requriment  and need help on DAX. There is a report which pulls data from project details and Invoice tables.

When user selects the project either single select or multi selct. The data from invoice table should shown up based on Project start date.

Example:  Revenue should be picked up based on project start date >= invoice date. The user either select mutli or single selection of project name from the slicer.

 

Project Details  Invoice Table   
Project NameStart date Project NameInvoice DateRevPicked up
A1/1/2021 A1/1/2020 $ 100.00No
B1/1/2020 A1/5/2020 $ 200.00No
   A1/1/2021 $ 300.00Yes
   A2/1/2021 $ 400.00Yes
   B1/1/2019 $ 500.00No
   B1/1/2020 $ 100.00Yes
   B1/1/2021 $ 100.00Yes
   B2/1/2021 $ 200.00Yes
Need Help
  • v-luwang-msft's avatar
    v-luwang-msft
    4 years ago

    Hi Anonymous ,

    Try measure like below :

    test1 = 
CALCULATE (
    MAX ( 'Project Details'[Start date] ),
    FILTER (
        ALLSELECTED ( 'Project Details' ),
        MAX ( 'Invoice Table'[Project Name] ) = 'Project Details'[Project Name]
    )
)
    test2 = 
CALCULATE (
    SUM ( 'Invoice Table'[Rev] ),
    FILTER (
        ALLSELECTED ( 'Project Details' ),
        MAX ( 'Invoice Table'[Project Name] ) = 'Project Details'[Project Name]
            && MAX ( 'Invoice Table'[Invoice Date] ) >= [test1]
    )
)
    test3 = SUMX('Invoice Table',[test2])

    Final get :

    Did I answer your question? Mark my post as a solution!


    Best Regards

    Lucien

2 Replies

  • v-luwang-msft's avatar
    v-luwang-msft
    Community Support
    4 years ago

    Hi Anonymous ,

    Try measure like below :

    test1 = 
CALCULATE (
    MAX ( 'Project Details'[Start date] ),
    FILTER (
        ALLSELECTED ( 'Project Details' ),
        MAX ( 'Invoice Table'[Project Name] ) = 'Project Details'[Project Name]
    )
)
    test2 = 
CALCULATE (
    SUM ( 'Invoice Table'[Rev] ),
    FILTER (
        ALLSELECTED ( 'Project Details' ),
        MAX ( 'Invoice Table'[Project Name] ) = 'Project Details'[Project Name]
            && MAX ( 'Invoice Table'[Invoice Date] ) >= [test1]
    )
)
    test3 = SUMX('Invoice Table',[test2])

    Final get :

    Did I answer your question? Mark my post as a solution!


    Best Regards

    Lucien