Calc period no using calendar tavle

[AliBb]

I have a calendar table with the following columns

Date. . calperiod commperiodnumber
15/3/2018. 3. 3
30/3/2018. 3 4

This is linked to a transaction table on date=transdate with following columns

Transdate postdate
15/3/2018. 30//32018
30/3//2018. 15/3/2018
30/3/2018. 30/3/2018

I want to write this in Dax but unsure how to pull the commperiodnumber for postdate

If commperiodnumber(transdate) >= commperiodnumber(postdate) + 1 then commperiodnumber(transdate) - 1 = PeriodNo
Or
If commperiodnumber(transdate) < commperiodnumber(postdate) + 1 then commperiodnumber(transdate) = PeriodNo
Or
If commperiodnumber(transdate) < commperiodnumbet(postdate) then commperiodnumber(postdate) = PeriodNo

The result for the three transactions listed should be 4, 3, 4

 

[rer433]

I have very same problem. any experts there ?

 

[Matt Allington]

There are 2 approaches. Either create a second inactive relationship (see my article here https://exceleratorbi.com.au/multiple-relationships-between-tables-in-dax/ )

...or use The function LOOKUPVALUE to fetch it.

 

[AliBb]

Thanks.
could you give an example of the syntax for lookupvalue within the IF statement please
Would this be more efficient than the userrelatiimship option?

 

[Matt Allington]

It depends what you mean by “pull the [number]”. What are you trying to do? Are you writing a measure or a calculated column?