Forum Discussion
join greater than less than
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
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
2 Replies
v-huizhn-msftMicrosoft Employee
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,
AngeliaHello Angelia, sorry for the delay I was out of my office.
Thanks for you solution
Sylvain
