Delayed posting of financial data based on Accounting books closing date

[egrospe17]

Hello PowerBI Gods,

I'm trying to put together a P&L dashboard and one of the components is showing Revenue, direct costs, operating costs and gross profit. My report is directly connected to a SQL database so it pulls latest data on a daily basis. We have a schedule when we close our books for the prior month. Currently, if i plot the data, in a chart, it will show months that aren't closed yet per the schedule. How am i able to tell PowerBI to "hide" the corresponding month's financial data until 1 day after the closing schedule?

Financial data looks something like this:

Posting Period	Expense Code	Amount
Jan-24	400 - Revenue	$           3,000,000.00
Jan-24	500 - Direct Cost	$             (200,000.00)
Jan-24	600 - Operating Cost	$             (180,000.00)
Feb-24	400 - Revenue	$           3,500,000.00
Feb-24	500 - Direct Cost	$             (350,000.00)
Feb-24	600 - Operating Cost	$             (180,000.00)
Mar-24	400 - Revenue	$           5,000,000.00
Mar-24	500 - Direct Cost	$             (300,000.00)
Mar-24	600 - Operating Cost	$             (180,000.00)
Apr-24	400 - Revenue	$           6,000,000.00
Apr-24	500 - Direct Cost	$             (500,000.00)
Apr-24	600 - Operating Cost	$             (180,000.00)
May-24	400 - Revenue	$           3,000,000.00
May-24	500 - Direct Cost	$             (150,000.00)
May-24	600 - Operating Cost	$             (180,000.00)
Jun-24	400 - Revenue	$           8,000,000.00
Jun-24	500 - Direct Cost	$         (1,000,000.00)
Jun-24	600 - Operating Cost	$             (180,000.00)
Jul-24	400 - Revenue	$           4,000,000.00
Jul-24	500 - Direct Cost	$             (350,000.00)
Jul-24	600 - Operating Cost	$             (250,000.00)
Aug-24	400 - Revenue	$               200,000.00
Aug-24	500 - Direct Cost	$               (20,000.00)
Aug-24	600 - Operating Cost	$                                 -

Closing Schedule

ID	Month	Closing Schedule
1	Dec-23	1/15/2024
2	Jan-24	2/14/2024
3	Feb-24	3/14/2024
4	Mar-24	4/12/2024
5	Apr-24	5/14/2024
6	May-24	6/14/2024
7	Jun-24	7/12/2024
8	Jul-24	8/13/2024
9	Aug-24	9/16/2024
10	Sep-24	10/14/2024
11	Oct-24	11/14/2024
12	Nov-24	12/13/2024

With the example data above, the end goal is to only show financial data from Jan-24 to Jun-24. Come 8/14/2024, I would like for the July-24 data to show. Obviously, any future months (if any data available) should not show as well. Below is the sample of how i would like it too look like

Any help would be appreciated, thanks!

Emmanuel

[DataInsights · Accepted solution]

@egrospe17,

There are various ways to achieve this. If your model has a date table (recommended), you can adapt this solution to use the date table.

Relationship:

Calculated column in Financial Data table:

Closed Period = 
VAR vToday =
    TODAY ()
VAR vCloseDate =
    RELATED ( 'Closing Schedule'[Closing Schedule] )
VAR vResult =
    IF ( vCloseDate <= vToday, 1, 0 )
RETURN
    vResult

Measure:

Amount Closed Periods = 
CALCULATE ( SUM ( 'Financial Data'[Amount] ), 'Financial Data'[Closed Period] = 1 )

Result:

[egrospe17]

Worked like a charm!! TYSM!