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

[Anonymous]

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

[ChiragGarg2512]

Check all the options and data types.

[Anonymous]

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.

[ChiragGarg2512]

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.

[Anonymous]

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

[ChiragGarg2512]

@Anonymous , try writing a dax for the column.

Like this:

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

[Anonymous]

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.

[ChiragGarg2512]

Playing and changing the expression can lead to desired outcome.

[Anonymous]

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