Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Calculate Monthly Difference DAX

Hello Power Users,   I'm trying to create a measure or calculation for Monthly difference. but not getting exactly what i need. Basically what i'm trying to do get the difference for Amount for eac...
Need Help
  • v-zhangti's avatar
    v-zhangti
    4 years ago

    Hi, Anonymous 

     

    You can try the following methods.

    Preamount =
VAR PreDate =
    MAXX (
        FILTER (
            ALL ( 'Table'[Entry_Date], 'Table'[Milestonenumber] ),
            [Entry_Date] < SELECTEDVALUE ( 'Table'[Entry_Date] )
                && [Milestonenumber] = SELECTEDVALUE ( 'Table'[Milestonenumber] )
        ),
        'Table'[Entry_Date]
    )
VAR Preamount =
    CALCULATE (
        SUM ( 'Table'[amount] ),
        FILTER (
            ALL ( 'Table' ),
            [Entry_Date] = PreDate
                && [Milestonenumber] = SELECTEDVALUE ( 'Table'[Milestonenumber] )
        )
    )
RETURN
    Preamount

    Difference = IF([Preamount]<>BLANK(),[Preamount]-SUM('Table'[amount]))

    Is this the result you expect?

     

    Best Regards,

    Community Support Team _Charlotte

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

v-zhangti's avatar
v-zhangti
Community Support
4 years ago

Hi, Anonymous 

 

You can try the following methods.

Preamount =
VAR PreDate =
    MAXX (
        FILTER (
            ALL ( 'Table'[Entry_Date], 'Table'[Milestonenumber] ),
            [Entry_Date] < SELECTEDVALUE ( 'Table'[Entry_Date] )
                && [Milestonenumber] = SELECTEDVALUE ( 'Table'[Milestonenumber] )
        ),
        'Table'[Entry_Date]
    )
VAR Preamount =
    CALCULATE (
        SUM ( 'Table'[amount] ),
        FILTER (
            ALL ( 'Table' ),
            [Entry_Date] = PreDate
                && [Milestonenumber] = SELECTEDVALUE ( 'Table'[Milestonenumber] )
        )
    )
RETURN
    Preamount

Difference = IF([Preamount]<>BLANK(),[Preamount]-SUM('Table'[amount]))

Is this the result you expect?

 

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Thank you so much