Forum Discussion

prakash11440278's avatar
prakash11440278
Icon for Post Prodigy rankPost Prodigy
5 years ago
Solved

Grand Total substract with previous record count

Hi Team,

Please share the dax expression for the below requirement.

 

 

Need Help
  • sayaliredij's avatar
    sayaliredij
    5 years ago

    Thank you for the data.

     

    You can create a measure using the following formula.

    It will be best if you create a calculated column by summing up all the columns in every row. to get total roll up amount

     

    Total =
    VAR totalvalue =
    CALCULATE ( SUM ( 'Table'[RollUp] ), REMOVEFILTERS ( 'Table' ) )
    VAR monthvalue =
    CALCULATE ( SUM ( 'Table'[RollUp] ) )
    VAR Total =
    IF ( ISBLANK ( monthvalue ), BLANK (), totalvalue )
    VAR CurrentDate =
    MAX ( 'Table'[Month] )
    VAR rollupYTD =
    CALCULATE (
    SUM ( 'Table'[RollUp] ),
    FILTER ( ALL ( 'Table' ), 'Table'[Month] < CurrentDate )
    )
    VAR result = Total - rollupYTD
    RETURN
    result

     You can get result as follows

     Thanks,

    Sayali 

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

4 Replies

  • sayaliredij's avatar
    sayaliredij
    Icon for Solution Sage rankSolution Sage
    5 years ago

    Thank you for the data.

     

    You can create a measure using the following formula.

    It will be best if you create a calculated column by summing up all the columns in every row. to get total roll up amount

     

    Total =
    VAR totalvalue =
    CALCULATE ( SUM ( 'Table'[RollUp] ), REMOVEFILTERS ( 'Table' ) )
    VAR monthvalue =
    CALCULATE ( SUM ( 'Table'[RollUp] ) )
    VAR Total =
    IF ( ISBLANK ( monthvalue ), BLANK (), totalvalue )
    VAR CurrentDate =
    MAX ( 'Table'[Month] )
    VAR rollupYTD =
    CALCULATE (
    SUM ( 'Table'[RollUp] ),
    FILTER ( ALL ( 'Table' ), 'Table'[Month] < CurrentDate )
    )
    VAR result = Total - rollupYTD
    RETURN
    result

     You can get result as follows

     Thanks,

    Sayali 

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

  • sayaliredij's avatar
    sayaliredij
    Icon for Solution Sage rankSolution Sage
    5 years ago

    Could you provide how the data looks like?  In this example, it is not possible to know how we get the first value.

     

    Regards,

    Sayali

      • v-easonf-msft's avatar
        v-easonf-msft
        Icon for Community Support rankCommunity Support
        5 years ago

        Hi, prakash11440278 

        Does sayaliredij ’s solution  can work  for you?

        If it is,  please mark the helpful replies or add your reply as Answered to close this thread.
        It will help other community members easily find the solution when they get the similar issue.

        Best Regards,
        Community Support Team _ Eason