Calculated list of months based off condition

[Chris2038]

I am trying to generate a list of month-end dates based off a single condition. For example, if the user selects 2 as the interval, the list should show ever 2nd month from the latest (max) date. See below for the Power BI file with the data. I am trying to do this as a calculated DAX table, as shown in the Month Listing column.https://1drv.ms/u/s!AnSxu76Gaw6ZsHVyvyqfRon7OoQL?e=TCEssK 

If 1 is selected as an interval, the list should show {12/31/21, 11/30/21, 10/31/21...etc}

If 2 is selected as an interval, the list should show {12/31/21, 10/31/21, 8/31/21... etc}

If 6 is selected as an interval, the list should show {12/31/21, 6/30/21, 12/31/20...etc}

[johnt75 · Accepted solution]

You can't do it exactly as you planned because a calculated table cannot react to a slicer, it is only calculated during data load. However, you can do something similar by creating a table which has a list of dates and the interval, for all allowed intervals. You can then use that interval column as the slicer and it will behave as I think you want it to

Month Listing = 
VAR numMonths =
    COUNTROWS( VALUES( 'Calendar Table'[End of Month] ) )
VAR maxDate =
    MAX( 'Calendar Table'[Dates] )
RETURN
    SELECTCOLUMNS(
        GENERATE(
            Intervals,
            VAR currentInterval = Intervals[Intervals]
            RETURN
                ADDCOLUMNS(
                    GENERATESERIES( 0, numMonths, currentInterval ),
                    "End of month", EOMONTH( maxDate, - [Value] )
                )
        ),
        "Intervals", [Intervals],
        "End of month", [End of month]
    )

[Chris2038]

This worked, however when bringing the Inverval slicer over to to the visual, I am restricted to 1 and 2. The others do not show up.  Perhaps because I have the date selection on Feb 28th?  This needs to work dymanically depending on a date selection

[johnt75]

You can trying using Edit Interactions and turn off the interaction between the date slicer and the interval slicer

[Chris2038]

I am having trouble tracing this against the PBIX file. In the Return section, what does Table[Value] represent?

[amitchandak]

@Chris2038 , You can get these measures or var in the measure

// Selected Value Measure is giving the number 2 or 3 

new measure =
var _max = maxx(allselected(Date),Date[Date])

Var _end = eomonth(_max,-1*[Selected Value Measure])  // end date of that month

Var _start = eomonth(_max,-1*[Selected Value Measure] -1 ) +1 //Start Date of that month

return

calculate( sum(Table[Value]), filter('Table', 'Table'[Date] >=_start && 'Table'[Date] <=_end))