Two columns for months and sequence passed months

[Anonymous]

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 ....

[AntonioM]

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]

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.

[AntonioM]

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

[AntonioM · Accepted solution]

Hi @Anonymous 

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

Month Table = 
ADDCOLUMNS (
    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