I have a calendar table that I want to use in Power BI - its a non-std calendar so normal time intelligence does not work.
I need to create a monthid index that changes at every change in fiscal period
eg
Date Fiscal Period Month ID
1/1/19 1 1
2/1/19 1 1
3/1/19 2 2
4/1/19 2 2
all the way down to 12 the new year would start at 13
1/1/20 1 13
2/1/20 1 13
3/1/20 1 13
4/1/20 2 14
etc
I've tried the group By (having created a MthnYear column) , added an index but this gets rid of all the other rows.
Do I have to create a separate table for this index and then merge it back in? or is there a 'one step' verision that allows me to do it within the existing calendar table?
Hi Alison,
You need two queries, the first one is your calendar query, the second one references your calendar query and groups the dates by MthYear. You add an index column to this second query.
Then you can merge the two queries bringing in the index number column from the second query to the first query.
Mynda
Thank you
