Issues with dax formula using date snapshots

Hi Cadams ,

I think you can try to use this DAX code:

Pipeline (Created) =
VAR SelectedSnapshotDate = MAX('Billed Pipeline Snapshot'[Snapshot Date])

VAR LatestSnapshotDate = CALCULATE(MAX('Billed Pipeline Snapshot'[Snapshot Date]), ALL('Billed Pipeline Snapshot'))

VAR SelectedSnapshot =
CALCULATETABLE(
    'Billed Pipeline Snapshot',
    'Billed Pipeline Snapshot'[Snapshot Date] = SelectedSnapshotDate
)

VAR LatestSnapshot =
CALCULATETABLE(
    'Billed Pipeline Snapshot',
    'Billed Pipeline Snapshot'[Snapshot Date] = LatestSnapshotDate
)

VAR AddedOpportunities =
EXCEPT(
    SELECTCOLUMNS(SelectedSnapshot, "OpportunityID", 'Billed Pipeline Snapshot'[Opportunity ID]),
    SELECTCOLUMNS(LatestSnapshot, "OpportunityID", 'Billed Pipeline Snapshot'[Opportunity ID])
)

RETURN
IF (
    ISFILTERED('Billed Pipeline Snapshot'[Snapshot Date]),
    SUMX(
        FILTER(SelectedSnapshot, 'Billed Pipeline Snapshot'[Opportunity ID] IN AddedOpportunities),
        'Billed Pipeline Snapshot'[Pipeline]
    ),
    BLANK()
)

Please provide example data and .pbix files if you can, so that the problem can be solved in a more in-depth study.

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.