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

[andycrabbe]

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 Movement). There is a value we track which is a measure called 'IFC / Closed' and this is snapshot on a daily basis so we know which client had what value on a given date ('Snapshot_Date').

I have a matrix visual where I have Client (and a couple of related fields) as rows, and Snapshot_Date as a filter, where users can select a starting date and an end date. In my matrix I want to give users a simple view that shows the 'IFC / Closed' value on the first date selected (with 0 if no data on that date), what it was on the last date selected (with 0 if no data on that date), and the difference between the two values.

This feels like it should be straight forward, but everything I try seems to not account for blank rows on the dates selected, and will show data for the first and last dates within the range that have a value, which isn't quite what I need.

Any help would be much appreciated! Thanks in advance

[dufoq3 · Accepted solution]

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]

That worked, thank you so much!

[dufoq3]

You're welcome. 😉

[andycrabbe]

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!

[dufoq3]

I've edited my previous post and reattached new file. Check it and let me know.

[lbendlin]

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-...

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/1447...

[andycrabbe]

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.