Forum Discussion

andycrabbe's avatar
andycrabbe
Frequent Visitor
2 years ago
Solved

Difference between values that occurred on 'Between' date filter values, allowing for blanks

Hi there   I've tried ChatGPT for this without success so here is my first post here, hoping someone can help!   I have a set of financial forecasting data per client (table called Forecast Movem...
Need Help
  • dufoq3's avatar
    dufoq3
    2 years ago

    Hi andycrabbe, edited code, new pbix file attached:

     

    Result:

     

    First Date SUM:

    First Date SUM = 
VAR _firstDate = CALCULATE(MIN(Table1[Snapshot_Date]), ALLEXCEPT(Table1, Table1[Snapshot_Date]))

VAR _result = 
    CALCULATE(
        SUM(Table1[IFC / Closed]),
        Table1[Snapshot_Date] = _firstDate
    )

RETURN _result

     

    Last Date SUM:

    Last Date SUM = 
VAR _lastDate = CALCULATE(MAX(Table1[Snapshot_Date]), ALLEXCEPT(Table1, Table1[Snapshot_Date]))

VAR _result = 
    CALCULATE(
        SUM(Table1[IFC / Closed]),
        Table1[Snapshot_Date] = _lastDate
    )

RETURN _result

     

dufoq3's avatar
dufoq3
Community Champion
2 years ago

Hi andycrabbe, edited code, new pbix file attached:

 

Result:

 

First Date SUM:

First Date SUM = 
VAR _firstDate = CALCULATE(MIN(Table1[Snapshot_Date]), ALLEXCEPT(Table1, Table1[Snapshot_Date]))

VAR _result = 
    CALCULATE(
        SUM(Table1[IFC / Closed]),
        Table1[Snapshot_Date] = _firstDate
    )

RETURN _result

 

Last Date SUM:

Last Date SUM = 
VAR _lastDate = CALCULATE(MAX(Table1[Snapshot_Date]), ALLEXCEPT(Table1, Table1[Snapshot_Date]))

VAR _result = 
    CALCULATE(
        SUM(Table1[IFC / Closed]),
        Table1[Snapshot_Date] = _lastDate
    )

RETURN _result

 

andycrabbe's avatar
andycrabbe
Frequent Visitor
2 years ago

That worked, thank you so much!

  • dufoq3's avatar
    dufoq3
    Community Champion
    2 years ago

    You're welcome. 😉