Get variation between two values for only the last date

[salemhaddad]

Hello,

I have a table with two value A & B. each them is group by data and segment.

I have to get the variation between the values A & B between the different Quarter and displayed only for the current date.

i made this DAX query but does not work correcly:

VariationTest =
VAR __mostRecentVisibleDate =
CALCULATE ( MAX ( Calendrier[Date] ); ALLSELECTED ( 'Calendrier' ) )
VAR __previousVisibleDate =
PREVIOUSQUARTER(Calendrier[Max Date])
VAR A = [sumamount]
VAR B =
CALCULATE (
[sumamount];
ALLSELECTED ( 'Calendrier' );
Calendrier[Date] = __previousVisibleDate
)
VAR __variancePercent =
DIVIDE ( B - A ; A )
RETURN
IF (
MAX ( Calendrier[Date] ) = __mostRecentVisibleDate;
__variancePercent
)

As PREVIOUSQUARTER does not take directly  __mostRecentVisibleDate, I create Max Date column with 

Max Date = MAX(Calendrier[Date])

Can someone help on this please.

Thank you in advance

[salemhaddad]

Thank you very much, I will try that

[Anonymous]

Hi @salemhaddad 

Please try the following Dax:

VariationTest =
VAR __mostRecentVisibleDate = 
    MAXX(
        ALLSELECTED('Calendrier'),
        'Calendrier'[Date]
    )
VAR __startOfQuarter = 
    STARTOFQUARTER(__mostRecentVisibleDate)
VAR __startOfPreviousQuarter = 
    STARTOFQUARTER(
        EDATE(__mostRecentVisibleDate, -3)
    )
VAR CurrentQuarterAmount = 
    CALCULATE(
        [sumamount],
        DATESBETWEEN(
            'Calendrier'[Date],
            __startOfQuarter,
            __mostRecentVisibleDate
        )
    )
VAR PreviousQuarterAmount = 
    CALCULATE(
        [sumamount],
        DATESBETWEEN(
            'Calendrier'[Date],
            __startOfPreviousQuarter,
            EDATE(__startOfQuarter, -1)
        )
    )
VAR __variancePercent = 
    DIVIDE(
        PreviousQuarterAmount - CurrentQuarterAmount, 
        CurrentQuarterAmount
    )
RETURN
    IF(
        MAX('Calendrier'[Date]) = __mostRecentVisibleDate,
        __variancePercent,
        BLANK()
    )

Best Regards,

Jayleny

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