Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

How to get previous year value to the current year value

Hello,


Can you please help me achieve the ask. Please follow the SS first

 

I need to display the values under MININCURRED for valuation Date 07/15/2020 beside valuation date 07/15/2021. I need to elimiate the values displayed under valuation date 07/15/2021 under MININCURRED i.e, for valuation date 07/15/2021, MAXINCURRED is 82,039 and MININCURRED should be 879 for the cause Stain or Injury by Lifting and following the same for the rest. 

Let me tell you the Dax queires used. :

MAXVAL : MAXVAL = CALCULATE([Total Incurred_NEW],
FILTER('clm ValuationData',MAX('clm ValuationData'[ValuationDate])),
(DATESBETWEEN('clm Claims'[LossDate].[Date],DATE(2021,1,1),DATE(2021,7,15))))
MAXINCURRED: 

MAXINCURRED =

CALCULATE(
'clm ValuationData'[Total Incurred_NEW],
FILTER('clm ValuationData',
('clm ValuationData'[MAXVAL])))

 

MINVAL: MinVAL = CALCULATE([Total Incurred_NEW],
FILTER('clm ValuationData',MAX('clm ValuationData'[ValuationDate])),
PREVIOUSYEAR(DATESBETWEEN('clm Claims'[LossDate].[Date],DATE(2021,1,1),DATE(2021,7,15))))

 

MININCURRED =
CALCULATE(
'clm ValuationData'[Total Incurred_NEW],
FILTER('clm ValuationData',
'clm ValuationData'[MinVAL]))

 

If the used Dax is not upto the mark, please help me achieve the above ask if there are any other methods. 

Please help

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous,

    I'd like to suggest you use the date function to manually get the previous date for calculating. It is more simple to custom the calculations and not required a calendar table.

    Measure=
VAR currDate =
    MAX ( 'clm ValuationData'[ValuationDate] )
RETURN
    CALCULATE (
        [Total Incurred_NEW],//replace this part to your calculated formula or value field
        FILTER (
            ALLSELECTED ( 'clm ValuationData' ),
            'clm ValuationData'[LossDate]
                = DATE ( YEAR ( currDate ) - 1, MONTH ( currDate ), DAY ( currDate ) )
        )
    )

    Regards,

    Xiaoxin Sheng

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous,

    I'd like to suggest you use the date function to manually get the previous date for calculating. It is more simple to custom the calculations and not required a calendar table.

    Measure=
VAR currDate =
    MAX ( 'clm ValuationData'[ValuationDate] )
RETURN
    CALCULATE (
        [Total Incurred_NEW],//replace this part to your calculated formula or value field
        FILTER (
            ALLSELECTED ( 'clm ValuationData' ),
            'clm ValuationData'[LossDate]
                = DATE ( YEAR ( currDate ) - 1, MONTH ( currDate ), DAY ( currDate ) )
        )
    )

    Regards,

    Xiaoxin Sheng