Forum Discussion
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...
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 _resultLast 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
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
Hi dufoq3 thanks for your time on this but I don't think that'll work. The minimum and maximum date values need to change according to what the user selects in the filter, whereas I think what you are suggesting will only work for the first and last dates in the source data. Let me know if I've misunderstood though!
I've edited my previous post and reattached new file. Check it and let me know.