DAX Looping Variable

Question

I'm building a Power BI dashboard that includes actual cost vs budget. Summarizing the cost is not a problem. The budget amount is entered in a table as a weekly amount, for example $1,000. We use a custom accounting calendar that dictates the number of weeks per month. The second month of each quarter is 5 weeks while all other months are 4 weeks. Here's what it looks like: January is 4 weeks February is 5 weeks March is 4 weeks April is 4 weeks May is 5 weeks June is 4 weeks July is 4 weeks August is 5 weeks September is 4 weeks October is 4 weeks November is 5 weeks December is 4 weeks

I need a looping variable in DAX that will simply count the number of weeks based on the months chosen with the month slicer. Then I can multiply the number of weeks by the weekly budget amount.

Here's a practical example: If I choose Jan, Feb, Mar, Apr then the number of weeks should be 17 weeks. And the budget amount should be $17,000.

I figured this out in Crystal Reports. Here's the formula I wrote:

local datetimevar startdate:= {?BegMonth};
local numbervar numofWeeks;
While startdate <= {?EndMonth}
Do(
if month(startdate) in [2,5,8,11] then
numofWeeks := numofWeeks + 5
else
numofWeeks := numofWeeks + 4;
startdate := DATEADD("m",1,startdate);
);
numofWeeks

I've tried several approaches with DAX but I got nothing to work.

Please provide a sample data screenshot along with and expected output. — Gangula
Please have a look at the example I made in an other question, it should work for you also: stackoverflow.com/questions/57413629/… (do not forget to upvote that answer if it works for you). — Aldert

Alexis Olson Alexis Olson · Accepted Answer · 2019-08-14T15:02:36

DAX doesn't exactly do looping. It does aggregations on filtered columns.

If you don't have a Month column, then create a calculated column.

TableName[Month] = MONTH ( TableName[Date] )

Once you have that, adding up weeks can be done with an iterator function.

Weeks =
SUMX (
    VALUES ( TableName[Month] ),
    IF ( TableName[Month] IN { 2, 5, 8, 11 }, 5, 4 )
)

The VALUES function is a list of unique values in the filter context, so if you selected Jan - Apr, then it would be {1,2,3,4}. Then SUMX iterates ("loops") over those four values and add 5 or 4 depending on the condition specified in the IF function.

DAX Looping Variable

1 Answers