calculating a date

[AliBb]

I have transactions that are imported via power query therefore the whole data set is updated each time

In power pivot I have a calendar table linked with transaction table by date - commissions are calculated on a date part way through the month on the data that is available at the time.

The calendar table includes includes a calculated date column for commission period - ie 1 week prior to month end
MOD(MONTH($A2+7)-1+AND(DAY($A2+7)>27,WEEKDAY($A2+7,13)>3,DAY($A2)-WEEKDAY($A2+7,13)>23)-AND(DAY($A2+7)<3,WEEKDAY($A2+7,14)<3,DAY($A2+7)<=WEEKDAY($A2+7,14)),12)+1

In addition to normal transactions that are dated in the normal way I have a routine that is run throughout the month (I have no control over this) that enters transactions into the table but always uses the month end date as the transaction date. Therefore at the time of calculating the commissions I could have the following data set:

Routine run on 15/03/2018 - data available as at 23/03/2018
[TABLE="width: 500"]
<tbody>[TR]
[TD]TransDate
[/TD]
[TD]Amount
[/TD]
[/TR]
[TR]
[TD]14/03/2018
[/TD]
[TD]500.00
[/TD]
[/TR]
[TR]
[TD]30/03/2018
[/TD]
[TD]1000.00
[/TD]
[/TR]
</tbody>[/TABLE]

Data used in commission calculation £1500.00

at month end a routine is run that can add data for the period in question -

Routine run on 30/03/2018
[TABLE="width: 500"]
<tbody>[TR]
[TD]TransDate
[/TD]
[TD]Amount
[/TD]
[/TR]
[TR]
[TD]14/03/2018
[/TD]
[TD]500.00
[/TD]
[/TR]
[TR]
[TD]30/03/2018
[/TD]
[TD]1000.00
[/TD]
[/TR]
[TR]
[TD]21/03/2018
[/TD]
[TD]250.00
[/TD]
[/TR]
[TR]
[TD]30/03/2018
[/TD]
[TD]5000.00
[/TD]
[/TR]
</tbody>[/TABLE]

Commission calculated on 20/04/2018 (commission period 24/03/2018 20/04/2018)

Data used in commission calculation 1000+250+5000

Problem is that the £1000 had already been taken into account in the commission for March therefore I need to differentiate between the dates of the transactions that have a month end date by somehow using the PostedDate.

Problem with the PostedDate is that the month end routine can post transactions for future periods ie

[TABLE="width: 500"]
<tbody>[TR]
[TD]TransDate
[/TD]
[TD]Amount
[/TD]
[TD]PostedDate
[/TD]
[/TR]
[TR]
[TD]30/03/2018
[/TD]
[TD]1000.00
[/TD]
[TD]15/03/18
[/TD]
[/TR]
[TR]
[TD]30/05/2018
[/TD]
[TD]3000.00
[/TD]
[TD]15/03/18
[/TD]
[/TR]
</tbody>[/TABLE]

Commission calculation for March would only take into account the 1000 because 30/05/2018 would not have been in the original calculation however when using the slicer to look at historic data the May transaction would have been pulled in


I need to be able to look at the Posted Date and TransDate to check which period it would have originally been assigned to give a calculated Period Number

The desired result for period number would be:

[TABLE="width: 500"]
<tbody>[TR]
[TD]TransDate
[/TD]
[TD]Amount
[/TD]
[TD]PostedDate
[/TD]
[TD]PeriodNo
[/TD]
[/TR]
[TR]
[TD]14/03/2018[/TD]
[TD]500.00
[/TD]
[TD]14/03/2018
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]30/03/2018
[/TD]
[TD]1000.00
[/TD]
[TD]15/03/2018
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]18/03/2018
[/TD]
[TD]250.00
[/TD]
[TD]22/03/2018
[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD]30/03/2018
[/TD]
[TD]5000.00
[/TD]
[TD]30/03/2018
[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD]30/05/2018
[/TD]
[TD]3000.00
[/TD]
[TD]15/03/2018
[/TD]
[TD]5
[/TD]
[/TR]
</tbody>[/TABLE]

Any ideas?

 

[cerfani]

Just a suggestion but you may want to shorten the post and ask a specific question.

 

[AliBb]

The question is how can I get the Period No. to calculate to the desired result table based on combination of the posteddate and the TransDate and commissionperiod (calculated using the formula referenced above) associated with those dates

I think this is what I'm trying to achieve

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

commperiodnumber(transdate) < commperiodnumber(postdate) + 1 then commperiodnumber(transdate) = PeriodNo​

I don't know how to do this with DAX in powerpivot