YTD Performance Comparison Only if Current Quarter Has Data

[diegoleonm]

Hi everyone,

I have a dataset that contains the following columns:

account(string), year(string), quarter(string), revenue(number).

I created a matrix with year and quarter as columns, account as the row, and sum of revenue as the values.

What I want to do is at the end of the matrix (last column) have one more column that compares year to date revenue with the last year.

For example, my dataset has revenue data from 2023 and 2024, so the last column should give me a % increase/decrease in each account's revenue only if the current year already has data (in this case would be Q1 and Q2), and as we have Q3 results, that column considers that data as well.

Any ideas?

[Anonymous · Accepted solution]

Hi @diegoleonm ,

You can try formula like below to create measure:

CurrentYearYTDRevenue = 
CALCULATE(
    SUM('YourDataset'[revenue]),
    DATESYTD('DateTable'[Date], "31/12"),
    'YourDataset'[Year] = YEAR(TODAY())
)

PreviousYearYTDRevenue = 
CALCULATE(
    SUM('YourDataset'[revenue]),
    DATESYTD('DateTable'[Date], "31/12"),
    'YourDataset'[Year] = YEAR(TODAY()) - 1
)

YTDPerformance = 
IF (
    [CurrentYearYTDRevenue] = 0,
    BLANK(),
    DIVIDE (
        [CurrentYearYTDRevenue] - [PreviousYearYTDRevenue],
        [PreviousYearYTDRevenue],
        0
    )
)

Best Regards,
Adamk Kong

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

[diegoleonm]

Thank you that works!