Count of open entries for fiscal month

[gio1082]

Hi, 

I need to create a table showing number of open entries that where open in a fiscal months

I have two tables 

1. Calendar date by ffiscal month 

Calendar Date	Fiscal Year	Fiscal Month

2. Data entry 

ID	OPEN_DT	LAST_CLOSE_DT

I need to create a table that shows open

Fiscal Month	# Open Entries in month
01 MAY
02 JUN

So for example if I have 3 open entries right now ( 01 May), and one closes in  03 JUL. 

[v-yangliu-msft · Accepted solution]

Hi  @gio1082  ,

This is the data I created:

Table1:

Table2:

Here are the steps you can follow：

1. Create calculated table.

Fiscal Month = FORMAT('Table'[Calendar Date],"dd")&" "&FORMAT('Table'[Calendar Date],"mmm")

Result:

2. Create calculated column.

3. Result:

If my answer does not meet your expectations, can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-yangliu-msft · Accepted solution]

Hi  @gio1082 ,

Here are the steps you can follow：

1. Create calculated column.

Month =
MONTH('Conversion_To_GCH_CY_FY_Dates'[Calendar Date])

Day =
VALUE(LEFT('Conversion_To_GCH_CY_FY_Dates'[Fiscal Month],2))

Year = YEAR('Conversion_To_GCH_CY_FY_Dates'[Calendar Date])

date = DATE([Year],[Month],[Day])

Result：

2. Create calculated table.

Table =
SUMMARIZE(FILTER('Conversion_To_GCH_CY_FY_Dates','Conversion_To_GCH_CY_FY_Dates'[date]<>BLANK()),'Conversion_To_GCH_CY_FY_Dates'[Fiscal Month],Conversion_To_GCH_CY_FY_Dates[date])

Table 2 = SUMMARIZE('Table','Table'[Fiscal Month]," Open Entries in month",SUM('Table'[Column]))

3. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-yangliu-msft]

Hi  @gio1082 ,

You can upload PBIX to some place where you can store files, and then copy the url and place it on the forum.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-yangliu-msft · Accepted solution]

Hi  @gio1082  ,

This is the data I created:

Table1:

Table2:

Here are the steps you can follow：

1. Create calculated table.

Fiscal Month = FORMAT('Table'[Calendar Date],"dd")&" "&FORMAT('Table'[Calendar Date],"mmm")

Result:

2. Create calculated column.

3. Result:

If my answer does not meet your expectations, can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[gio1082]

Not sure how to submit a pbix file here, could you give details how to do it. I would be happy to give an example that way 

[gio1082]

Here is my Power BI Example:

https://www.dropbox.com/s/zjqdbk4vl8olgnu/Example%20Power%20BI.pbix?dl=0

(let me know if the link works) 

 What I looking for is to create table where it shows active entries (subscribers) by Fiscal Month. 

ID: 1 (3/21/2021-4/21/2021) Was open Fiscal month "11 Mar", "12 April" 

ID:2 (3/21/2021-5/21/2021)was open fiscal Month "11 Mar", "12 April", "01 May"

ID:3 (4/21/2021- )was open fiscal Month "12 April", "01 May"

(Note:So if Last_day Is null then is still open)