Forum Discussion
Create second Calendar table from an original Calendar table
Hi -
I a have calendar table with with two columns, Date [=Calendar(Date(2015, 1, 1), Date(2018, 12, 31)] and a Measure [= "Y" or "N"].
I want create a second calendar table that includes the Dates from the original where the measure equals "Y" (see tables below).
Thank you, Mark
| Original Table | Second Table | ||
| Date | Measure | Date | |
| 1/1/2015 | N | ||
| 1/2/2015 | N | ||
| 1/3/2015 | Y | 1/3/2015 | |
| 1/4/2015 | Y | ===> | 1/4/2015 |
| : | : | ||
| 12/30/2018 | Y | 12/30/2018 | |
| 12/31/2018 | N |
13 Replies
So, you should be able to do that like this:
New Calendar = VAR __table = ADDCOLUMNS(ALL('Date'),"__Measure",[Measure]) RETURN FILTER(__table,[__Measure] = "Y")Hi Greg - The new calendar didn't seem to work, as it seemd to ignore the filter, and shows the entire range of the original calendar.
Would you have any suggestions?
The sample code was adjusted as shown below ...
NEW_CALENDAR = VAR __table = ADDCOLUMNS(ALL('CALENDAR'[Date]),"__Measure",'CALENDAR'[Is In Date Range]) RETURN FILTER('CALENDAR','CALENDAR'[Is In Date Range] = "Y")A picture of the original calendar and new calander is below. I am wanting the New Calendar to show only 5/12/2018 to 5/20/2018
Anonymous, not sure, I created it in my test environment and it seems to work. See attached Page 10. Calendar, New Calendar and Measure 4.
