Using MMM-YY with a Year value

[OuluChris]

Hi,

I set up a Calendar table which includes all the columns I've needed (so far!). I mostly use the MMM-YY column which I then use extensively in pivot tables in Excel to see things at the month level.

However, I now have some data that is broken down only at the year level. I want the same value that is stored for the year to be applied in every month of that year but I can't find a way of doing this.

I can't create a relationship between my tables because the year appears 365 times in the calendar table and many times in the data table so they contain duplicates and would create a many-to-many relationship.

Any ideas will be gratefully received.

Chris

[Jimmy801]

Hello @OuluChris 

you could also create a year-table and use this for the relationship of your rates- and calendar-table

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[OuluChris]

I tried that but I couldn't make it work.

I extracted the year column from the rates table and deleted duplicates. I then linked that year table to the rates table and to the calendar table.

When I select a Month-Year value in a pivot table there aren't any values shown. All the values are in a (blank) field.

[Jimmy801]

Hello @OuluChris 

what values you would like to show? The rates value?

Then you probably need a custom measure. 

here the measure

Rates:=CALCULATE(sum(Rates[Rate]);filter(values(Rates[Year]);COUNTROWS(RELATEDTABLE('Calendar'))>0))

Here my data

here my datamodel

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[OuluChris]

My data looks like this which means that the relationships can't flow in the direction you showed and therefore it doesn't work.

Chris

[Jimmy801]

Hello @OuluChris 

any news here?

BR

Jimmy

[Jimmy801]

Hello @OuluChris 

it can flow. Just use the measure in my post. And see my outcome as well, it's working

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[OuluChris · Accepted solution]

I cannot make this work because (I think) my data is not arranged in the same way as the example.

In the end, I came up with a cheat as follows:

CALCULATE(SUM([Rate]),DATESINPERIOD('Calendar'[Date], MIN('Calendar'[Date]),-12,MONTH))

Not very elegant but seems to do what I wanted.

[lbendlin]

Create a fake date column in your rates table (for example first day of the year) and link that to your calendar table. Then use a measure to spread the year's value over the mmm-yy column.

[OuluChris]

Please can you explain how to "use a measure to spread the year's value over the mmm-yy column"?