Forum Discussion

CasperSV's avatar
CasperSV
Icon for Helper II rankHelper II
4 years ago
Solved

Date filter with specified periods

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

Need Help
  • timg's avatar
    timg
    4 years ago

    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

2 Replies

  • timg's avatar
    timg
    Icon for Solution Sage rankSolution Sage
    4 years ago

    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