join greater than less than

[sdionne]

Hello, I have 2 tables:

one it is a calendar that made by the DAX function:

Calendrar = CALENDAR('Paramètre'[DateDeb];'Paramètre'[DateFin])

Where DateDeb and DateFin are from parameters

And a second that indicate Accounting Period (see file)Accounting Periods

The special thing in this case is that the accounting period change from one year to other:

Exemple:

the 2017 accounting year begin 2016-10-30

the 1st month in 2017 is from 2016-10-30 to 2016-12-03

and in 2018 accounting year begin 2017-10-29

the 1st month in 2018 is from 2017-10-29 to 2017-12-02

How can i join the table to have in calendar the accounting period?

Date	AccYear	CmptPer
2017-10-27	2017	12
2017-10-28	2017	12
2017-10-29	2018	1
2017-10-30	2018	1
2017-10-31	2018	1

Thanks

sdionne

[v-huizhn-msft · Accepted solution]

Hi @sdionne,

After long time research and test, I can only get part of it, I share with you for reference and will continue to study.

First, unpit the Begin and End columns in Feuil1 table. Please review my query statement.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwNFfSUTI0AhEG+ob6MAEDfSMLCCdWB6zMAiQKlbGEKzPSN4KqigUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [YearCmpt = _t, CmptPer = _t, Begin = _t, End = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"YearCmpt", Int64.Type}, {"CmptPer", Int64.Type}, {"Begin", type date}, {"End", type date}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"YearCmpt", "CmptPer"}, "Attribute", "Value")
in
    #"Unpivoted Columns"

Then create calculated columns using the formula, but only get part of expected result.

Column = RELATED(Feuil1[CmptPer])
Column 2 = RELATED(Feuil1[Attribute])
CmptPer = VAR SS=CALCULATE(FIRSTNONBLANK('Calendar'[Value],1),FILTER('Calendar','Calendar'[Column 2]="Begin"))
            VAR EE=CALCULATE(FIRSTNONBLANK('Calendar'[Value],1),FILTER('Calendar','Calendar'[Column 2]="End"))
            VAR  R=RELATED(Feuil1[CmptPer])
RETURN 
IF(AND('Calendar'[Date]>=SS,'Calendar'[Date]<=EE),12)

Best Regards,
Angelia

[sdionne]

Hello Angelia, sorry for the delay I was out of my office.

Thanks for you solution

Sylvain