##Urgent || Listing Application names based on their first billing date

[DivyaA]

I have 2 tables named Focus and calendar. I have columns named Billing cost, application name from Focus Table (To be visualized as matrix). From Calendar table, I'm taking Year and month as seperate slicers.

Requirement is to list the application names based on their first billing date. For example: If I select Jan and 2024, it must display the applications that started its billing from Jan.

Similarly I need to get the application list that started billing in the last 30/60/90/120 days and 1/2/3 months( Have seperate period table with these contents).

Say I select Feb and 2025, it must list the application names based on the specified period.

I've been trying to sort this through multiple ways(for almost a week now) but couldn't achieve it. It would be great if anyone could help me in this case.

Thanks in advance!! 

[v-karpurapud]

Hi @DivyaA 

We are following up once again regarding your query. Could you please confirm if the issue has been resolved?If so, kindly mark the helpful response and accept it as the solution to assist other community members in resolving similar issues more efficiently. If not, please provide detailed information so we can better assist you.

Thank you for your understanding and participation.

[DivyaA]

Hi,
The solutions provided in here isn't resolving my issue. Anyways thanks for providing your support.

[v-karpurapud · Accepted solution]

Hi @DivyaA 

Thank you for the update. We understand that the issue persists on your end despite applying the suggested solution. The DAX measure and data model structure provided are technically appropriate for filtering applications by their first billing date, whether for a specific month/year or for a dynamic range such as the last 30, 60, or 90 days, or 1, 2, or 3 months. If the expected results are not being achieved, we recommend reviewing the configuration of the date slicers, ensuring that the calendar table is correctly marked as a Date table, and confirming that the IsFirstBillingInSelectedPeriod measure is being used to filter the visual. Additionally, please verify that the relationships are set as described and that the matrix references the correct field.

Thank you for being part of Fabric Community Forum.

Regards,
Karpurapu D,
Microsoft Fabric Community Support Team.

[v-karpurapud]

Hi @DivyaA 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If the above response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[ABD128]

Hi @DivyaA 

Consider the below steps:

1. Create a summary table in DAX to get each application's first billing date:

ApplicationFirstBilling =

ADDCOLUMNS (

   VALUES ( Focus[Application Name] ),

   "First Billing Date", CALCULATE ( MIN ( Focus[Billing Date] ) )

)

2. Create model relationships by linking Focus[Application Name] to ApplicationFirstBilling[Application Name] and Calendar[Date] to ApplicationFirstBilling[First Billing Date], while keeping PeriodTable disconnected.

3. Create a measure to check if the first billing date falls in the selected period:

IsFirstBillingInSelectedPeriod =

VAR Days = MAX(PeriodTable[Num Days])

VAR Months = MAX(PeriodTable[Num Months])

VAR RefDate = MAX(Calendar[Date])

VAR FirstBilling = CALCULATE(MIN(Focus[Billing Date]), ALLEXCEPT(Focus, Focus[Application Name]))

VAR FromDate = SWITCH(

   TRUE(),

   NOT ISBLANK(Days), RefDate - Days,

   NOT ISBLANK(Months), EDATE(RefDate, -Months)

)

RETURN IF(FirstBilling >= FromDate && FirstBilling <= RefDate, 1, 0)

In the visual, use Focus[Application Name] in a matrix, add slicers for Calendar[Year], Calendar[Month], and PeriodTable[Period Label], and apply a visual or page-level filter where IsFirstBillingInSelectedPeriod = 1

[v-karpurapud]

Hi @DivyaA 

We are following up to see if you have had the chance to review the information provided. If you have any further questions, please do not hesitate to contact us. Could you confirm whether your query has been resolved? If so, kindly mark the helpful response and accept it as the solution to assist other community members in resolving similar issues more efficiently. If not, please provide detailed information so we can better assist you.

Thank You.

[DivyaA]

Hi @v-karpurapud ,
No , My query isn't resolved yet so I couldn't mark the solution.

[DivyaA]

Here is the sample layout I'm expecting. I couldn't get the dataset (Sorry☹️)!! 

[danextian]

It would be easier for us to come up with a solution had you provided a workable sample data (not an image) and your expected result fromt that.  A link to an Excel file or a sanitized copy of your PBIX is fine (confidential data removed). Just as it takes time to prepare your sample data, it also takes time to come up with a solution.

[Nasif_Azam]

Hey @DivyaA ,

To achieve your goal in Power BI of listing application names based on their first billing date in conjunction with selected Year, Month, or a relative period (like last 30/60/90 days, or 1/2/3 months), here's a complete approach that uses DAX and your existing data model.

• Focus Table contains: Application Name, Billing Cost, and Billing Date (if not present, you must derive it using your calendar or from Billing Cost entries)

• Calendar Table is properly related to Focus[Billing Date]

• You have a separate Period Table with values like 30, 60, 90, 120, 1 month, 2 months, etc.

Objective 1: Show Applications with Their First Billing Date

Calculated Column (in Focus Table):

FirstBillingDate = 
CALCULATE(
    MIN(Focus[Billing Date]),
    ALLEXCEPT(Focus, Focus[Application Name])
)

Option A: Using Year & Month Slicers (from Calendar Table)

Create a measure to show applications whose first billing matches the selected year/month.

ShowApps_FirstBillingInSelectedMonth = 
VAR SelectedYear = SELECTEDVALUE('Calendar'[Year])
VAR SelectedMonth = SELECTEDVALUE('Calendar'[MonthNumber]) -- use numeric month

RETURN
IF(
    SELECTEDVALUE(Focus[FirstBillingDate]) <> BLANK() &&
    YEAR(SELECTEDVALUE(Focus[FirstBillingDate])) = SelectedYear &&
    MONTH(SELECTEDVALUE(Focus[FirstBillingDate])) = SelectedMonth,
    1,
    0
)

Use this measure as a visual-level filter in your matrix: ShowApps_FirstBillingInSelectedMonth = 1

 

Objective 2: Show Applications Based on Last 30/60/90/120 Days or Last 1/2/3 Months

Step 1: Create a measure to calculate first billing per app

FirstBillingDateMeasure = 
CALCULATE(
    MIN(Focus[Billing Date]),
    ALLEXCEPT(Focus, Focus[Application Name])
)

Step 2: Create a measure to dynamically filter based on selected period

(Assuming Period Table[Days] has values like 30, 60, 90, 120…)

ShowApps_FirstBillingInPeriod = 
VAR SelectedDays = SELECTEDVALUE('Period Table'[Days])
VAR MaxDate = MAX('Calendar'[Date]) -- or TODAY(), depending on your logic
VAR MinDate = MaxDate - SelectedDays
VAR FirstBill = [FirstBillingDateMeasure]

RETURN
IF(
    NOT ISBLANK(FirstBill) &&
    FirstBill >= MinDate &&
    FirstBill <= MaxDate,
    1,
    0
)

You can also write another version using months:

ShowApps_FirstBillingInMonths = 
VAR SelectedMonths = SELECTEDVALUE('Period Table'[Months])
VAR MaxDate = MAX('Calendar'[Date]) -- or TODAY()
VAR MinDate = EDATE(MaxDate, -SelectedMonths)
VAR FirstBill = [FirstBillingDateMeasure]

RETURN
IF(
    NOT ISBLANK(FirstBill) &&
    FirstBill >= MinDate &&
    FirstBill <= MaxDate,
    1,
    0
)

Again, use this measure as a filter: ShowApps_FirstBillingInPeriod = 1 or = TRUE()

For Detailed Information:

Working with Date/Calendar Tables

Using EDATE for Month Calculations

Relative Date Filtering in Power BI

Creating a Matrix Visual in Power BI

Community Thread on First Billing Logic

If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.

Best Regards,
Nasif Azam

[burakkaragoz]

Hi @DivyaA ,

This is a classic use case for identifying the first billing date per application and then filtering based on that. Here's how you can approach it in Power BI:

1. Create a calculated column in the Focus table to get the first billing date per application:

FirstBillingDate = 
CALCULATE(
    MIN(Focus[BillingDate]),
    ALLEXCEPT(Focus, Focus[ApplicationName])
)

1. Then, create a measure to check if the first billing date falls within the selected period:

IsFirstBillingInPeriod = 
VAR SelectedStartDate = MIN(Calendar[Date])
VAR SelectedEndDate = MAX(Calendar[Date])
RETURN
IF(
    MIN(Focus[FirstBillingDate]) >= SelectedStartDate &&
    MIN(Focus[FirstBillingDate]) <= SelectedEndDate,
    1,
    0
)

1. Use this measure as a visual-level filter in your matrix to show only applications where IsFirstBillingInPeriod = 1.

For the rolling periods (like last 30/60/90 days), you can use your period table to dynamically calculate the date range and apply similar logic.

Let me know if you need help wiring this up with your period table — happy to help further.

If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.

translation and formatting supported by AI

[DivyaA]

@burakkaragoz Can you pls help me out for the rolling periods as well. I've share a screenshot of my current visual.

[johnt75]

You could create a table which holds all the distinct application names along with the first billing date. You could do this in Power Query by referencing the Focus table as a new query and grouping by application choose the min of the billing date column, or you could use DAX like

Applications =
ADDCOLUMNS (
    DISTINCT ( Focus[Application Name] ),
    "First Billing Date", CALCULATE ( MIN ( Focus[Billing Date] ) )
)

Either way, link 'Calendar'[Date] to Applications[First Billing Date] and link Applications[Application Name] to Focus[Application Name] .

[DivyaA]

Thanks John,This solution works for specific month wise visualization.
But how can I map this with period table to get details such as 30/60/90 days or 1/2/3 months?

[johnt75]

Once you have the calendar table linked to first billing date you can use standard approaches to time intelligence calculations.

If you want to show applications with a first billing date in the last 30, 60, 90 days for example you can create a table which includes those dates, e.g.

Date Periods =
UNION (
    ADDCOLUMNS (
        DATESINPERIOD ( 'Date'[Date], TODAY (), -30, DAY ),
        "Num Days", 30
    ),
    ADDCOLUMNS (
        DATESINPERIOD ( 'Date'[Date], TODAY (), -60, DAY ),
        "Num Days", 60
    ),
    ADDCOLUMNS (
        DATESINPERIOD ( 'Date'[Date], TODAY (), -90, DAY ),
        "Num Days", 90
    )
)

Link this in a many-to-many, single direction relationship so that Date Periods filters Date. You can then use Date Periods in visuals to show those applications.