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
Anonymous's avatar
Anonymous
Not applicable
1 year ago

Hi dmimp,

 

Thank you for reaching out to the Microsoft Fabric Forum Community.

 

Try below DAX to get the desired result

Upsell Total =
VAR SelectedName = SELECTEDVALUE(Merge1[Name])

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

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

RETURN
LastEvent - FirstEvent


If you find this response helpful, please consider marking it as the accepted solution and giving it a thumbs-up to support others in the community.


Thanks & Regards,
Prasanna Kumar

  • dmimp's avatar
    dmimp
    Frequent Visitor
    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