Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Chris2038
Frequent Visitor

Calculated list of months based off condition

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}

1 ACCEPTED SOLUTION
johnt75
Super User
Super User

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]
    )

View solution in original post

5 REPLIES 5
johnt75
Super User
Super User

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]
    )

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

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

Chris2038
Frequent Visitor

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

amitchandak
Super User
Super User

@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))

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.