Showing results for 
Search instead for 
Did you mean: 
Helper II
Helper II

Array list

I'm trying to create a array in DAX and have my calculations using that list for filtering. Is this possible in DAX.


Total Backlog = 
var Quarters =
MONTH(TODAY()) in {1,2,3}, {"2020 Q1"},
MONTH(TODAY()) in {4,5,6}, {"2020 Q1", "2020 Q2"},
MONTH(TODAY()) in {7,8,9}, {"2020 Q1", "2020 Q2", "2020 Q3"},
MONTH(TODAY()) in {10,11,12}, {"2020 Q1", "2020 Q2", "2020 Q3", "2020 Q4"}

return CALCULATE(sum('Arc_Inventory_Export (Raw)'[BestEstCost])
, 'Arc_Inventory_Export (Raw)'[Status] in {"Construction", "Design", "Follow-up Needed", "Inspection Needed", "Project Identified", "Ready to Schedule", "Scheduled", "Inspection Complete"}
, FILTER('PSK Table', 'PSK Table'[PSK] in {"PSK403", "PSK404", "PSK405"})
, 'Arc_Inventory_Export (Raw)'[CompletionQuarter] in Quarters
, 'Arc_Inventory_Export (Raw)'[Blank Complete Date & Blank Quarter] = "0")


Community Support
Community Support

Hi @adjohnson2 ,


I modified your formula to below.


Total Backlog =
VAR MonthToday =
    MONTH ( TODAY () )
VAR Quarters =
    SWITCH (
        TRUE (),
        MonthToday IN { 1, 2, 3 }, "2020 Q1",
        MonthToday IN { 4, 5, 6 }, "2020 Q1, 2020 Q2",
        MonthToday IN { 7, 8, 9 }, "2020 Q1, 2020 Q2, 2020 Q3",
        MonthToday IN { 10, 11, 12 }, "2020 Q1, 2020 Q2, 2020 Q3, 2020 Q4"
        SUM ( 'Arc_Inventory_Export (Raw)'[BestEstCost] ),
        FILTER (
            'PSK Table',
            'PSK Table'[PSK] IN { "PSK403", "PSK404", "PSK405" }
                && SEARCH ( 'Arc_Inventory_Export (Raw)'[CompletionQuarter], Quarters,, 999 ) <> 999
                && MAX ( 'Arc_Inventory_Export (Raw)'[Blank Complete Date & Blank Quarter] ) = "0"
                && MAX ( 'Arc_Inventory_Export (Raw)'[Status] )
                    IN {
                    "Follow-up Needed",
                    "Inspection Needed",
                    "Project Identified",
                    "Ready to Schedule",
                    "Inspection Complete"



And if you want to do it with M in query editor, you could reference this similar thread:




Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Instead of that approach, I would add a column to have the quarter as an integer (1-4), store it in a variable called thisquarter in your measure, and use FILTER(ALL(Table[Quarter]), Table[Quarter] <= thisquarter) in your CALCULATE instead of the "in Quarters" approach (also add a term to filter it to the current year).





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.

@mahoneypa HoosierBI on YouTube

I literally don't understand what you said.

Helpful resources

PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

MPPC 2023 PBI Carousel

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors