Returning Dateset of Specific Dates Dynamically From Parameter

[seabrew]

I would like to create a report that returns data from last month, the month from the prior year, and all calendar quarter end dates in-between. Is this possible this date logic in Power BI and to build around a parameter datetime value?

Examples (MM/DD/YY):

Report Date: 2/28/17 (This would be a parameter) 

Report Query Dates: 2/28/17, 12/31/2016, 9/30/16, 6/30/16, 3/31/16, 2/29/16

Report Date: 1/31/17 (This would be a parameter) 

Report Query Dates: 1/31/17, 12/31/2016, 9/30/16, 6/30/16, 3/31/16, 1/31/16

Report Date: 12/31/16 (This would be a parameter) 

Report Query Dates: 12/31/16, 9/30/16, 6/30/16, 3/31/16 12/31/16

[Phil_Seamark · Accepted solution]

Hi @seabrew

This calculated table should returns the dates you specified in a table 

Return Dates = 
VAR ReportDate = DATE(2017,2,28) ---<<< your parameter date here

VAR EndOfSameMonthLastYear = EOMONTH(ReportDate,-12)

VAR Quarters = 
    SELECTCOLUMNS(
    FILTER(ADDCOLUMNS(
    CALENDAR(
        EndOfSameMonthLastYear,ReportDate
        ),
        "Start Of Quarter" , 
        IF( MONTH([Date]) in {3,6,9,12} && DAY([Date]) = 1 ,1 ,0)
        ,"End Of Quarter" , EOMONTH([Date],0)
        ),[Start Of Quarter]=1),"End Of Quarter",[End Of Quarter])

VAR Months = FILTER(
        CALENDAR(
        EndOfSameMonthLastYear,ReportDate
        ),
        [Date] = ReportDate
        || [Date]= EndOfSameMonthLastYear

        )
        
RETURN UNION (Months,  Quarters)  
        

[seabrew]

Hi @Phil_Seamark,

Thank you! This works great! Many, many thanks.