Forum Discussion

dmimp's avatar
dmimp
Frequent Visitor
1 year ago
Solved

Calculate difference between Amount based on most recent and least recent date

Hello, I export event sales monthly on our clients (12 tables, combined using Append).  Each table has a "name" key column.  From there, I have several Amount columns.  I want to show the change ...
Need Help
  • dmimp's avatar
    dmimp
    1 year ago

    Small edits and it worked!  Thank you SO MUCH!!!!

    ------

    UpsellTotal =
    VAR SelectedName = SELECTEDVALUE('Merge1'[Name])

    VAR FirstEvent =
    CALCULATE(
    MAX('Merge1'[Event Actual]),
    TOPN(1,FILTER('Merge1', 'Merged Data (2)'[Name] = SelectedName &&
    NOT(ISBLANK('Merge1'[Event Actual]))),'Merge1'[Export Date].[Date], ASC))

    VAR LastEvent =
    CALCULATE(
    MAX('Merge1'[Event Actual]),
    TOPN(1,FILTER('Merge1', 'Merge1'[Name] = SelectedName && NOT(ISBLANK('Merge1'[Event Actual]))),'Merge1'[Export Date].[Date], DESC))

    RETURN
    LastEvent - FirstEvent
danextian's avatar
danextian
Icon for Super User rankSuper User
1 year ago

Hi dmimp 

 

Please provide a workable sample data (not an image), your expected result from the same sample data and your reasoning behind. You may post a link to Excel or a sanitized copy of your PBIX stored in the cloud. While providing sample data can be time-consuming, but so is coming up with a solution.

  • dmimp's avatar
    dmimp
    Frequent Visitor
    1 year ago

    Thank you (everyone) for your fast replies!  I've been working for a couple of hours on cleaning the data so that it can be public, but everything I dokeeps breaking the links, tables, calculations, etc.  Then when I think I've fixed it, the formulas aren't providing correct results.   I'll keep working on it again as soon as I can, and post ASAP.  I appreciate you all!