DAX Help

[Anonymous]

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 Name	Start date		Project Name	Invoice Date	Rev	Picked up
A	1/1/2021		A	1/1/2020	$ 100.00	No
B	1/1/2020		A	1/5/2020	$ 200.00	No
			A	1/1/2021	$ 300.00	Yes
			A	2/1/2021	$ 400.00	Yes
			B	1/1/2019	$ 500.00	No
			B	1/1/2020	$ 100.00	Yes
			B	1/1/2021	$ 100.00	Yes
			B	2/1/2021	$ 200.00	Yes

[v-luwang-msft · Accepted solution]

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

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.