Forum Discussion

OmarAmmar_95's avatar
OmarAmmar_95
Frequent Visitor
3 years ago
Solved

Year over Year change using DAX Problem !

I wrote a DAX function that calculates the number of benefiting individuals that equals one and if blank shows 0 as shown :

 

Total Benefiting Individuals =
IF (
    CALCULATE(COUNT('Beneficiary - updated'[BENEFICIARY_ID]),FILTER('Beneficiary - updated','Beneficiary - updated'[NUMBER_OF_BENEFICIARIES] = 1)) = BLANK (),
    0,
CALCULATE(COUNT('Beneficiary - updated'[BENEFICIARY_ID]),FILTER('Beneficiary - updated','Beneficiary - updated'[NUMBER_OF_BENEFICIARIES] = 1)))
 
Now I want to calculate the year over year change with a DAX fuction so I could visualize it on my dashboard as a number card,
can anyone help ?!!
Need Help
Show and Tell
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    3 years ago

    Hi OmarAmmar_95 ,

    According to your description, I create a sample.

    To calculate the year over year change of Total Benefiting Individuals, here's my solution, create a measure.

    YOY Change =
VAR _Pre =
    CALCULATE (
        [Total Benefiting Individuals],
        'Beneficiary - updated'[Year]
            = MAX ( 'Beneficiary - updated'[Year] ) - 1
    )
RETURN
    IF (
        _Pre = BLANK (),
        BLANK (),
        DIVIDE ( [Total Benefiting Individuals] - _Pre, [Total Benefiting Individuals] )
    )

    Get the result.

    I attach my sample below for your reference.

     

    Best Regards,
    Community Support Team _ kalyj

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

     

2 Replies

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Community Support
    3 years ago

    Hi OmarAmmar_95 ,

    According to your description, I create a sample.

    To calculate the year over year change of Total Benefiting Individuals, here's my solution, create a measure.

    YOY Change =
VAR _Pre =
    CALCULATE (
        [Total Benefiting Individuals],
        'Beneficiary - updated'[Year]
            = MAX ( 'Beneficiary - updated'[Year] ) - 1
    )
RETURN
    IF (
        _Pre = BLANK (),
        BLANK (),
        DIVIDE ( [Total Benefiting Individuals] - _Pre, [Total Benefiting Individuals] )
    )

    Get the result.

    I attach my sample below for your reference.

     

    Best Regards,
    Community Support Team _ kalyj

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

     

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    3 years ago

    Hi,

    You can simplify your measure to

    Total Benefiting Individuals = COALESCE(CALCULATE(COUNT('Beneficiary - updated'[BENEFICIARY_ID]),'Beneficiary - updated'[NUMBER_OF_BENEFICIARIES] = 1),0)

    To calculated y-o-y change, you will need a Calendar Table.  If you have that, you can use the PREVIOUSYEAR() function.