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

     

lbendlin's avatar
lbendlin
Super User
2 years ago

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

andycrabbe's avatar
andycrabbe
Frequent Visitor
2 years ago

Thank you. Here's a link to an excel file that contains some sample data and desired output Sample forecast data.xlsx

The First Date and Last Date columns are not in the data but you can see from the formulas what I'm trying to achieve - this is what I can't recreate using DAX to ultimately give me the difference between the two values.

 

Let me know if you need anything else.