YTD Calculations (with a twist)

[ostur]

I'm trying to create a YTD calculation, but because my data is set up in a weird way (I know, I'm working on it) I am having a hard time creating a YTD calculation in PowerBI.

Here below is a screenshot of how it is set up:

And I am trying to set up a view like this:

Where I compare performances over time. Simple so far.

But what I'm trying to show is both a view where I can show the performance data for only in Month nr.2 but also show YTD performance comparisons (Months 1+2), ideally for all years.

Whats the easiest way to do that?

Thank you for your help!

[v-cgao-msft]

Hi @ostur ,

1. First make a duplicate of the table in Power Query, Then complete all the years and months for each record.

2. Make another copy of the table and unpivot the three columns.

3. Merge query and expand value:

Table2:

4. In desktop, Please new a measure like:

YTD = CALCULATE(SUM('Table2'[Value]),'Table2'[Month]<=MAX('Table2'[Month]))

result:

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[ostur]

Hi Gao,

Thank you very much for the feedback and assistance.

In general I agree that this data structure makes more sense, and I'm working towards that, but right now I'm locked into this specific data structure (i.e. different columns for different values).

Working in that framework, is there a solution?

Thank you,

Tryggvi