cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Anonymous
Not applicable

## Two columns for months and sequence passed months

Hi guys,

I want to create a table of two columns.

First colum contains numbers from 1 to 12. These are months of the fiscal year, fiscal year starts from month 10 to month 9

second column is for sequence of passed months like shown below in the table:

First month is 10 and passed months is 10

Second month is 11 and passed months are 10 and 11

And so on ....

1 ACCEPTED SOLUTION
Solution Sage

Hi @Anonymous

It is possible. There could be other ways but this is all I've got to work so far.

``````Month Table =
GENERATESERIES ( 1, 78 ),
"Month",
MOD ( FLOOR ( POWER ( 2 * [Value], 1 / 2 ) + 1 / 2 + 8, 1 ), 12 ) + 1,
"Sequence Passed Months",
VAR m = floor((sqrt(8*[Value]+1) - 1) / 2, 1)
RETURN
MOD ( [Value] - m*(m+1)/2 + 1 + 8 , 12 ) + 1
)
``````

Have a check but that should give you what you're after. You can either delete or hide the 'Value' column afterwards.

Hope that helps

4 REPLIES 4
Solution Sage

Hi @Anonymous ,

You could do this in Power Query

``````let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1YlWMgKTxmDSBEyagkkzMGkOJi3ApCWYNDSAUBDdhkDtsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Numbers(10, Number.Mod([Column1]+2,12)+1)),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
#"Calculated Modulo" = Table.TransformColumns(#"Expanded Custom", {{"Custom", each Number.Mod(_-1, 12)+1, type number}})
in
#"Calculated Modulo"``````

The first two steps are just to create the list of numbers 1-12, you could skip those if you have that already.

Just curious, what is it you need this for? It could be something you can use time inteligence and DATESYTD for.

Anonymous
Not applicable

Is it possible to do this in dax ?

I am trying to use the first column for month selector. The idea is whenever a month is selected from the first column the visual should show data from october (start of the fiscal year) up to selected month.

Solution Sage

Alternatively you could just use 'enter data'. It's a bit of typing or you could create and copy it from excel easily enough.

Solution Sage

Hi @Anonymous

It is possible. There could be other ways but this is all I've got to work so far.

``````Month Table =
GENERATESERIES ( 1, 78 ),
"Month",
MOD ( FLOOR ( POWER ( 2 * [Value], 1 / 2 ) + 1 / 2 + 8, 1 ), 12 ) + 1,
"Sequence Passed Months",
VAR m = floor((sqrt(8*[Value]+1) - 1) / 2, 1)
RETURN
MOD ( [Value] - m*(m+1)/2 + 1 + 8 , 12 ) + 1
)
``````

Have a check but that should give you what you're after. You can either delete or hide the 'Value' column afterwards.

Hope that helps

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors