Best Practice re Calendar table, Modeling, etc.

[Txtcher]

I am a beginner with Power BI. I have the following tables:

- NFA Apps

- HX (Apps history)

- FEES

- FY2024 Calendar table (which I created to contain fiscal year 2024 dates)

I need to find all apps approved in FY2024, along with application history milestones. The Apps table does not have a date approved field. I have to get that from the HX (App History). The HX tables contains multiple different values (including one for Approved). It is a 1-many relationship with Apps. Here is the model I set up:

To find the dates of the milestones (including when it was approved), I need to find the min date for each value in the History table for each apps (group by ParentID and find min CreatedDate for each NewValue). I used a measure:  

License Issued = CALCULATE(

    Min([CreatedDate]),

    FILTER(HX,[NewValue]="Approved"),

            ALLEXCEPT('HX','HX'[ParentId]))

I also have several other measures created based on the HX and FEE table to find the min date of other events. These events will occur at any point in time (not just in FY2024), and always occur before the app is approved (License Issued).

To find the apps approved in FY2024, I thought I could add a second filter to the existing measure like this:

License Issued = CALCULATE(

    Min([CreatedDate]),

    FILTER(HX,[NewValue]="Approved"), 'FY2024 DATES'[Date],

            ALLEXCEPT('HX','HX'[ParentId]))

But when I put this measure into the report table containing the other measures from HX, I get Apps in the table that do not have a Licensse Issued (not Approved) because of the other measures that do not necessarily fall in FY2024 and do not contain the 2nd filter from the calendar table.

This problem demonstrates my inexperience. How do I fix this? What have I done incorrectly? Is it the way I wrote the measures? Or is it the calendar table?

[v-aatheeque]

Hi @Txtcher ,
Thanks for reaching out to Microsoft Fabric Community Forum.

To better understand the issue and help identify the root cause, could you please provide a small sample of your data from the NFA Apps, HX (History), FEES, and FY2024 Calendar tables?

Just a few representative rows from each would be really helpful.

Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

How to provide sample data in the Power BI Forum - Microsoft Fabric Community

[Txtcher]

Thank you for your response. Here are samples of the tables:

I think this is what would be described as the dimension table:  NFA Apps. This is all applications with a status of approved. For each of these apps, I need to create milestone timelines based on the history.

Id	Name	Account_Record_Type__c	Application_Status__c	Application_Type__c	Date_Submitted__c
aAB8y00000009heGAA	RSLAN-0032993	Licensing Applicant	Approved	NFA - Initial	null
aAB8y0000000BmlGAE	RSLAN-0000033159	Licensing Applicant	Approved	NFA - Initial	05/10/2024

This is the application history table called HX. the ParentId column matches the Id column from NFA Apps (above).

Id	ParentId	Field	NewValue	CreatedDate
0178y00001yAluSAAS	aAB8y00000009heGAA	Application_fee__c	TRUE	04/28/2022
0178y00002twVevAAE	aAB8y00000009heGAA	Application_Status__c	Response Required	08/31/2022
0178y00007VqzqNAAR	aAB8y00000009heGAA	Application_Status__c	In Review	02/14/2024
0178y000085P0osAAC	aAB8y00000009heGAA	Initiate_License_Fee__c	TRUE	04/21/2024
0178y000087tnKYAAY	aAB8y00000009heGAA	Initial_license_fee__c	TRUE	04/24/2024
0178y0000880wfQAAQ	aAB8y00000009heGAA	Application_Status__c	Approved	04/25/2024
0178y00003CbmRDAAZ	aAB8y0000000BmlGAE	Application_Status__c	Response Required	10/12/2022
0178y00003CbmREAAZ	aAB8y0000000BmlGAE	Application_fee__c	TRUE	10/12/2022
0178y00003xIEshAAG	aAB8y0000000BmlGAE	Application_Status__c	In Review	01/20/2023
0178y00007bBeyWAAS	aAB8y0000000BmlGAE	Application_Status__c	In Review	02/23/2024
0178y000089W0zGAAS	aAB8y0000000BmlGAE	Initiate_License_Fee__c	TRUE	04/28/2024
0178y00008ISGdaAAH	aAB8y0000000BmlGAE	Initial_license_fee__c	TRUE	05/15/2024
0178y00008JA83UAAT	aAB8y0000000BmlGAE	Application_Status__c	Approved	05/16/2024

This is the FEE table. From this table I can collect when the app was submitted based on the Application fee. I can also collect when the License fee was paid (part of the timeline I need to create).

Credentialing_Application__c	Name	Date_payment_was_received__c	Fee_Type__c
aAB8y000000018oGAA	FEE-0000040667	05/25/2022	Application Fee
aAB8y00000009heGAA	FEE-0000028209	04/14/2022	Application Fee
aAB8y00000001GYGAY	FEE-0000042318	04/28/2022	License Fee
aAB8y000000092BGAQ	FEE-0000042326	05/05/2022	License Fee
aAB8y0000000BmlGAE	FEE-0000042369	08/03/2022	Application Fee
aAB8y0000000BmlGAE	FEE-0000053515	05/15/2024	License Fee

I created my calendar table with this formula:  

FY2024 DATES = CALENDAR(DATE(2023, 9, 1),DATE(2024, 8, 31))

[Txtcher]

TMI, but here it is: These are the milestones I need to find for all applications that had a license issued in FY2024 (see calendar table above). I have created measures for each.

Milestone	Source table / field(s) / parameter	Measure
Submitted & Paid	Fees - Date payment was received FEE TYPE:  Application Fee	Submitted and Paid = CALCULATE(     MIN([Date_payment_was_received__c]),     FILTER(FEES,[Fee_Type__c]="Application Fee"),             ALLEXCEPT('FEES',FEES[Credentialing_Application__c]))
Response Required	HX - Min CreatedDate for [NewValue]=RESPONSE REQUIRED	Response Required = CALCULATE(     Min([CreatedDate]),     FILTER(HX,[NewValue]="Response Required"),             ALLEXCEPT('HX','HX'[ParentId]))
In Review	HX  - Max CreatedDate of [NewValue]=IN REVIEW	In Review = CALCULATE(     MAX([CreatedDate]),     FILTER(HX,[NewValue]="In Review"),             ALLEXCEPT('HX','HX'[ParentId]))
In Process	HX - Max CreatedDate of [NewValue]=IN PROCESS	In Process = CALCULATE(     MAX([CreatedDate]),     FILTER(HX,[NewValue]="In Process"),             ALLEXCEPT('HX','HX'[ParentId]))
License Fee Checkbox	HX - CreatedDate for [Field]=Initiate License Fee / [NewValue]=True	License Fee Checkbox = CALCULATE(     Min([CreatedDate]),     FILTER(HX,[Field]="Initiate_License_Fee__c"),             ALLEXCEPT('HX','HX'[ParentId]))
License Fee Paid	FEES - Date payment was received FEE TYPE: License Fee	License Fee Paid = CALCULATE(     MIN([Date_payment_was_received__c]),     FILTER(FEES,[Fee_Type__c]="License Fee"),             ALLEXCEPT('FEES',FEES[Credentialing_Application__c]))
License Issued	HX - CreatedDate of [NewValue]=Approved	License Issued = CALCULATE(     Min([CreatedDate]),     FILTER(HX,[NewValue]="Approved"),             ALLEXCEPT('HX','HX'[ParentId]))

The measures work, but I do not know how to filter the matrix (I think that's what you call my table visualization) for only those apps with a license issued in FY2024. I am not experienced enough to understand how to get there using the measures above and the calendar table. 😑

Next: calculate the number of days in between each milestone (also inserted into the matrix). Sample:

From Submitted and Paid to Response Required =

VAR Milestone1 = DATEDIFF(FEES[Submitted and Paid],HX[Response Required],DAY)

RETURN

If(

    Milestone1 < 0,

    BLANK(),

    if(

        Milestone1 = 0,

        1,

        Milestone1)

    )

Finally, I need a separate visual that provides the average number of days between milestones for each app type (only apps with a license issued in FY2024). A second problem in that I do not know how to filter the matrix for FY2024.

[v-aatheeque · Accepted solution]

Hi @Txtcher ,

To filter visuals for only FY2024-approved applications, follow these steps:

• Create a measure that checks if the application’s license issuance date falls between September 1, 2023, and August 31, 2024. If so, it returns 1 (approved for FY2024), otherwise 0. 
• Add the Is FY2024 Approved measure to the visual-level filters of your matrix and set it to show only records where the value equals 1.
• To calculate the days between the "Submitted and Paid" and "Response Required" dates, ensure both dates are not blank and that the start date is less than or equal to the end date. If these conditions hold, use the DATEDIFF function to calculate the days.
• Use the CALCULATE function with the AVERAGE function to calculate the average of the "Days Submitted to Response" measure, but only for records where the application is approved for FY2024.

This ensures that your milestones and averages are correctly filtered for FY2024-approved applications.

Hope it helps!

If this post was helpful, please consider marking Accept as solution to assist other members in finding it more easily.

If you continue to face issues, feel free to reach out to us for further assistance!