topic Re: Select all invoices between posting date and selected period in DAX Commands and Tips

Select all invoices between posting date and selected period

Khalidc — Fri, 17 Jun 2022 15:30:55 GMT

I have a sales invoice table presenting all historic invoices "CustomerInvoice". The invoice table has a column "posting period" and a column "closing period". The posting period is the period the invoice was posted, and closing is the period where the cash was received and invoice closed. I would like to populate an invoice subledger based on the selected period in a slicer which is coming from the period date table "visma_ financialPeriod".

I have created the following measure but it only returns the invoices having posting periods = selected financialperiod and not the invoices which where posted before the selected financial period:

It must be easy to solve, but I just cannot see it at this moment.

Re: Select all invoices between posting date and selected period

Anonymous — Fri, 17 Jun 2022 16:58:12 GMT

Hi Khalidc - I am not sure about the VAR for post and close period. Would something like the following work.

Account Receivable Subledger = 
VAR _period = SELECTEDVALUE ( Visma_financialperiod[Period] , MAX ( Visma_financialperiod[Period] ) )
RETURN
CALCULATE ( SUM ( Visma_customerInvoice[Amount] ) , 
       Visma_customerInvoice[postperiod adj] <= _period , 
       Visma_customerInvoice[closedfinancialperiod] > _period  || 
       ISBLANK( Visma_customerInvoice[closedfinancialperiod] 
)

Re: Select all invoices between posting date and selected period

Khalidc — Fri, 17 Jun 2022 22:22:35 GMT

Thanks!

First it returned the exact same result but after adding the ALL() to it, it worked, see in red!

CALCULATE(sum(Visma_customerInvoice[amount]),

all(Visma_financialPeriod),

Visma_customerInvoice[postperiod adj] <= _period ,

Visma_customerInvoice[closedFinancialPeriod] > _period ||

isblank (Visma_customerInvoice[closedFinancialPeriod]))