Forum Discussion

diegoleonm's avatar
diegoleonm
Frequent Visitor
2 years ago
Solved

YTD Performance Comparison Only if Current Quarter Has Data

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?

Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.