Forum Discussion
Using MMM-YY with a Year value
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
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.
9 Replies
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.
Please can you explain how to "use a measure to spread the year's value over the mmm-yy column"?
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
JimmyI 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.
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
