PowerBI - Creating a list of month numbers (1-12) to apply to another table

Question

I'm looking for some direction to achieve a usability change and my brain has just completely stalled.

I currently have 2 tables in my Power BI model:

Table 1: "FactTable". This contains a list of sales by product, by month Table 2: "TimePeriod". This contains a list of months by name

I've been asked to add a 3rd table: Table 3: "PeriodType". This contains a list of time-based definitions.

At the moment, the TimePeriod table is joined to the FactTable on the Month field, allowing the user to click one or more months to filter the contents of the FactTable, but I've had the request come back to implement "Year to date", "Quarter", "Full Year" and "year to go" instead of the user having to click on the months to select them.

What I'd like to achieve is this: if a user selects a month from TimePeriod Table and YTD from the PeriodType table, then that will select all the months upto and including the selected month.

ie in Feb, if we select YTD, then it will select the months JAN and FEB and apply those as a filter to the FactTable. if the user then changes the selection on the PeriodType visual from YTD to Full Year, then this will automatically switch the selection to include ALL the periods. Similarly, if they select "Quarter", then it will switch to select the current quarter's months (Jan, Feb, Mar), and if we change the selection to be "Year to Go" then this will select Feb, Mar...Nov, Dec.

I've added the tables into my Power BI model, but hit a mental block and dont know how to proceed.

If I were doing this in TSQL then I would select a list of the months from a datetable and the main query would have an "in" clause to select only the transactions in those months.

Answer 1

OK, so instead of just 2 tables, I've added to them:

Apologies for not putting in a diagram in the initial post - I didn't know how.

so now I can achieve what the user wanted and Select a period AND an attribute of that Period.

Worked Example File attached Link to pbix