Date filter with specified periods

[CasperSV]

Hi guys,

I have 2 queries; 1 with accounting periods and 1 with General ledger.

The accounting periods are as follow:

As you can see february and march (maart) are starting at 31-1-2022 and 28-2-2022. 

This is making it difficult... 

The slicer shows the following:

When I select february in 2022, it is showing all postings on the date 28-2-2022. 

However, it should show all postings between 31-1-2022 and 27-2-2022...

I have this relationship between the accounting periods and G/L ledger:

The accounting periods and G/L ledger are imported from Business Central and it should automatically update when editing the accounting periods in Business central.

Do you guys have any idea how to fix this?

Best regards,

Casper

[timg · Accepted solution]

Hi Casper,

I'd recommend working with a dimDate table (table containing one row for every date) for date analyses. There you can add calculated columns with custom definitions such as periods based on accounting periods. Once you have a dimDate table you could add the periods from your accounting periods table to this table with DAX. Below you'll find an example of how I added a column with customized periods to a dimDate table, based on an existing table containing accounting periods: 

Existing accounting periods table:

Accounting periods

Adding Accounting periods to dimDate:

result when using in visuals:

Hope that helps you getting started

Groetjes,

Tim

[CasperSV]

Dankjewel Tim!