Forum Discussion

Mohivaj's avatar
Mohivaj
Frequent Visitor
2 years ago
Solved

setting baseline value and compare the difference between baseline total for the rest of the months

Hi, I have a requirement to caluclate set a baseline (month total) and this value needs to be compared to total of the other months. I have attached the sample file. I have used th below formual but I did not get the desired results. The DAX expression provide the right value for April as 0 but for the May month it is not substracting from the baseline cost. 

Baseline_calculation =

IF(CALCULATE(SUM('Table'[Cost]),KEEPFILTERS('Calendar'[MonthNum]>4 )),

CALCULATE(SUM('Table'[Cost]),KEEPFILTERS('Calendar'[Month]="Apr" && 'Calendar'[Year]=2024 ))-CALCULATE(sum('Table'[Cost])
))

 

  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Mohivaj ,
    Based on the data you provided, you can try the following steps:
    Create a column

    Month_Number = MONTH('Table'[Date])

    Create a measure

    Baseline calculation = 
VAR _sumApr = 
CALCULATE(
    SUM('Table'[Cost]),
    FILTER(
        ALL('Table'),
        'Table'[Month_Number]= 4
    )
)
VAR _sumGroupBymonth = 
CALCULATE(
    SUM('Table'[Cost]),
    ALLEXCEPT(
        'Table',
        'Table'[Month_Number]
    )
)
RETURN
_sumGroupBymonth - _sumApr

    Final output

    Best regards,
    Albert He

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



2 Replies