Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
Khalidc
Frequent Visitor

Select all invoices between posting date and selected period

Hi

 

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:

 

Khalidc_2-1655479791876.png

 

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

 

 

1 ACCEPTED SOLUTION
Daryl-Lynch-Bzy
Resident Rockstar
Resident Rockstar

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]
)

 

 

 

View solution in original post

2 REPLIES 2
Daryl-Lynch-Bzy
Resident Rockstar
Resident Rockstar

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]
)

 

 

 

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]))

 

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.