topic how do i create a calculated column and use a loop to add 1,2,3 to 12? in DAX Commands and Tips

how do i create a calculated column and use a loop to add 1,2,3 to 12?

Anonymous — Wed, 20 Sep 2023 07:54:44 GMT

testTable

year	mth	forecasted_mth	forecasted_year
2023	9	9	2023
2023	9	10	2023
2023	9	11	2023
2023	9	12	2023
2023	9	1	2024
2023	9	2	2024
2023	9	3	2024
2023	9	4	2024
2023	9	5	2024
2023	9	6	2024
2023	9	7	2024
2023	9	8	2024

The table above is a sample of what I'm working on.

For context, I need a way to add 1 to forecasted_mth. If its more than 12, it would -12 from it. My real data would be much more than 12 rows, so this is just a sample.

I filled out forecasted_mth and forecasted_year to show what its supposed to look like.

I know PowerBI doesn't have loops, but is there a way to do what I want?

Thanks

Re: how do i create a calculated column and use a loop to add 1,2,3 to 12?

ChiragGarg2512 — Wed, 20 Sep 2023 08:13:09 GMT

Anonymous , try writing a dax for the column.

Like this:

Column = IF('Table (2)'[frcstd_mnth] = 12, -12, 'Table (2)'[frcstd_mnth]+1)

Re: how do i create a calculated column and use a loop to add 1,2,3 to 12?

Anonymous — Wed, 20 Sep 2023 08:18:43 GMT

hey thanks for the reply. Right now there isn't forecasted_mth and I'm trying to find it. I put added that column in the original post to show what the outcome is supposed to look like.

Re: how do i create a calculated column and use a loop to add 1,2,3 to 12?

ChiragGarg2512 — Wed, 20 Sep 2023 08:24:35 GMT

Playing and changing the expression can lead to desired outcome.

Re: how do i create a calculated column and use a loop to add 1,2,3 to 12?

Anonymous — Wed, 20 Sep 2023 08:30:59 GMT

[frcstd_mnth]+1 gives 10 for all rows. it doesn't increase the increment

Re: how do i create a calculated column and use a loop to add 1,2,3 to 12?

ChiragGarg2512 — Wed, 20 Sep 2023 08:47:25 GMT

Check all the options and data types.

Re: how do i create a calculated column and use a loop to add 1,2,3 to 12?

Anonymous — Wed, 20 Sep 2023 08:54:13 GMT

you're doing +1 for frcstd_mnth. I want to do it on mnth which is all 9's. So your dax doesn't really work.

What i want to achieve is +1 for the first row, +2 for the second, ..., and +12 for the last row.

Re: how do i create a calculated column and use a loop to add 1,2,3 to 12?

ChiragGarg2512 — Wed, 20 Sep 2023 09:06:10 GMT

You can introduce an INDEX column by going to powerQuery. Go to add column-> Index column -> from 1.

The expression then can be changed to adding index to mnth column and if that sum goes above 12, then the desired changes can be applied.

Re: how do i create a calculated column and use a loop to add 1,2,3 to 12?

Anonymous — Wed, 20 Sep 2023 09:10:45 GMT

well, that could work here. But my actual dataset is huge so i don't think index would work