Issues with dax formula using date snapshots

[Cadams]

Good afternoon all,

I've been trying for hours and cannot seem to get this formula to work.

I've built a powerbi with snapshots, where the snapshots are a combined set of CSV files that are merged together (all have the same column names etc) with an additional column that labels the date of snapshot.

What I'm trying to do is calculate which sales opportunities have been created between the very latest snapshot and the snapshot date that I filter based on a slicer (for this example, I've got a snapshot for 30/12, 06/01, 13/01 - where 13/01 is the latest snapshot).

The formula below works and calculates correctly. The only problem I have is that when I select the snapshot date, it is always compares the selected snapshot against the previous date (e.g. when I select 06/01, it compares 06/01 against 30/12), instead of comparing the selected snapshot against the latest snapshot.

What it should do is:

if I select 06/01, it should compare 06/01 to 13/01

If I select 30/12, it should compare 30/12 to 13/01

I've tried changing the formula to show only the latest snapshot with this: 

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

and
VAR SelectedSnapShotDate =
CALCULATE(
MAX('Billed Pipeline Snapshot'[Snapshot Date]),
FILTER('Billed Pipeline Snapshot', 'Billed Pipeline Snapshot'[Snapshot Date] < SelectedSnapshotDate)
)

But this just returns nothing.

Can anyone help me solve this please? 🙂

Original formula:

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

VAR PreviousSnapshotDate =
CALCULATE(
MAX('Billed Pipeline Snapshot'[Snapshot Date]),
FILTER(ALL('Billed Pipeline Snapshot'), 'Billed Pipeline Snapshot'[Snapshot Date] < SelectedSnapshotDate)
)

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

VAR PreviousSnapshot =
CALCULATETABLE(
'Billed Pipeline Snapshot',
'Billed Pipeline Snapshot'[Snapshot Date] = PreviousSnapshotDate
)

VAR AddedOpportunities =
EXCEPT(
SELECTCOLUMNS(SelectedSnapshot, "OpportunityID", 'Billed Pipeline Snapshot'[Opportunity ID]),
SELECTCOLUMNS(PreviousSnapshot, "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()
)

[Anonymous · Accepted solution]

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.