DAX Calculation Help - Appending and filtering records

[trabass]

I'm stuck figuring out the dax formula to achieve the following.

Have a table of data (Tasks) which I report off.  Been asked to take quarterly snapshots (Tasks Archive) to "lock" the values report in those quarters.  However, data in future quarters comes from the "Tasks Table".

I've taken the two tables and appended them together.  Now there are duplicate rows.  My pseudo logic that I'm trying to convert to DAX is as follows:

If Tasks Table (Task Finish Date) is less than the Tasks Archive Table (Archive Date) then return Tasks Archive Table Row values Except where Tasks Table has values different Tasks Archive Table Then

report row as exception to be fixed.

If Tasks Table (Task Finish Date) is greater than the Tasks Archive Table (Archive Date) then return Tasks Table Row values

Source	Tasks ID	Archive Date	Tasks Finish Date	Value	Row to Report (Y / N)
Tasks Archive	1	01/02/2018	31/01/2018	10	Y
Tasks	1		31/01/2018	10	N
Tasks Archive	2	01/02/2018	25/01/2018		N
Tasks	2		25/01/2018	5	Exception
Tasks Archive	3	01/02/2018	30/03/2018		N
Tasks	3		30/03/2018	10	Y

[Zubair_Muhammad]

@trabass

Try this calculated column.

Assuming your Table Name is "MergedTable"

Row to Report =
VAR FinishDate_is_Earlier =
    CALCULATE (
        FIRSTNONBLANK ( MergedTable[Tasks Finish Date], 1 ),
        FILTER (
            ALLEXCEPT ( MergedTable, MergedTable[Tasks ID] ),
            MergedTable[Source] = "Tasks Archive"
        )
    )
        < CALCULATE (
            FIRSTNONBLANK ( MergedTable[Archive Date], 1 ),
            FILTER (
                ALLEXCEPT ( MergedTable, MergedTable[Tasks ID] ),
                MergedTable[Source] = "Tasks Archive"
            )
        )
VAR Values_are_same =
    CALCULATE (
        SUM ( MergedTable[Value] ),
        FILTER (
            ALLEXCEPT ( MergedTable, MergedTable[Tasks ID] ),
            MergedTable[Source] = "Tasks Archive"
        )
    )
        = CALCULATE (
            SUM ( MergedTable[Value] ),
            FILTER (
                ALLEXCEPT ( MergedTable, MergedTable[Tasks ID] ),
                MergedTable[Source] = "Tasks"
            )
        )
VAR Exception_Check =
    IF (
        FinishDate_is_Earlier
            && Values_are_same = FALSE ()
            && MergedTable[Source] = "Tasks",
        "Exception"
    )
RETURN
    IF (
        Exception_Check = "Exception",
        "Exception",
        IF (
            FinishDate_is_Earlier && Values_are_same
                && MergedTable[Source] = "Tasks Archive",
            "Y",
            IF (
                FinishDate_is_Earlier = FALSE ()
                    && Values_are_same = FALSE ()
                    && MergedTable[Source] = "Tasks",
                "Y",
                "N"
            )
        )
    )

[Zubair_Muhammad]

@trabass

[trabass]

Hi @Zubair_Muhammad

Thank you very much for the reply.  I didn't know about using variables in dax - learning slowly...

I shouldn't of included "Source" as an example column.  Unless when you do an append can you add a column that identifies which table the row of data was appended from?

Working on changing the formula you provided (thnks). 

[Zubair_Muhammad · Accepted solution]

Hi @trabass

I think there is a way in QUERY Editor to get the SOURCE TABLE name.

Nevertheless...since it appears that Archive Date is Blank for TASKS table... you may add this calculated column to get the source

Source =
IF ( ISBLANK ( MergedTable[Archive Date] ), "Tasks", "Tasks Archive" )

[trabass]

@Zubair_Muhammad Thanks very much for your help.  Didn't think of capturing the source that way, seems to easy now.