cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
OuluChris
Frequent Visitor

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.

 

OuluChris_0-1613429688918.png

 

Any ideas will be gratefully received.

 

 

Chris

1 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.

View solution in original post

9 REPLIES 9
Jimmy801
Super User III
Super User III

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

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.

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

Jimmy801_0-1613485799100.png

 

here my datamodel

Jimmy801_1-1613485817669.png

 

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

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

 

1.PNG

 

2.PNG

 

Chris

Hello @OuluChris 

 

any news here?

 

BR

 

Jimmy

Hello @OuluChris 

 

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

Jimmy801_0-1613541901458.png

 

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

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.

View solution in original post

lbendlin
Super User III
Super User III

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"?

Helpful resources

Announcements
User Groups Public Preview

Join us for our User Group Public Preview!

Power BI User Groups are coming! Make sure you’re among the first to know when user groups go live for public preview.

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors