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

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

     

7 Replies

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

  • 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

      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's avatar
        dufoq3
        Community Champion
        2 years ago

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