Need help creating a measure to display Bonus Sales for the last financial year

[lucky_jyoti]

Hi, 

I am having trouble "thinking in DAX" WRT below proportion.

Trying to create a measure to calculate 'Bonus values'  for last financial year (Jan-Dec) ,to be paid in the month of FEB every year. There are actual values coming from 'Smartsheet' and forecast vaalues coming from 'Snowflake'. It should display in the report as one value only under 'FEB'. The functionality being expected is like one below. Display values only under FEB and other month remain blank for that measure.

 The tables used from the model are: 

The base Bonus sales values will be based on the below measure:

Bonus Expense =

VAR closing_date =

    DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 1 )

VAR date_context =

    DATE ( SELECTEDVALUE ( 'Date'[Year] ), SELECTEDVALUE ('Date'[Month] ), 1 )

VAR result =

    CALCULATE (

        SUM ( 'Actual and Planning Details'[Amount_orig] ),

        'Actual and Planning Details'[Version]

            = IF (

                date_context >= closing_date,

                SELECTEDVALUE ( 'VA - Compare Selections'[Version (Compare)] ),

                "Actuals"

            )

    )

  RETURN

    result

[Anonymous]

Hi @lucky_jyoti ,

(1)We can create two columns.

Month number= Month('Calendar'[Date])

Financial Year= YEAR('Calendar'[Date])

(2)We can create a measure.

Bonus Values = 
var _a= CALCULATE ( SUM ( 'Actual and Planning Details'[Bonus Expense] ), FILTER ( ALL('Calendar'), 'Calendar'[Financial Year] = MAX('Calendar'[Financial Year]) - 1 ))
return 
IF( MAX('Calendar'[Month Number] )= 2 ,_a,BLANK())

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

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

[lucky_jyoti]

Thank you Neeko for your reply and coming up this solution. 

Here is how we put it to work: 

IF(

      ISINSCOPE('Date'[Month Name]) && SELECTEDVALUE('Date'[Month Name]) = "February" ,

      CALCULATE(([Bonus Expense]), ALLEXCEPT('Date', 'Date'[Year]), PREVIOUSYEAR('Date'[Posting Date]))

  )