Forum Discussion
Best Practice re Calendar table, Modeling, etc.
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:
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!
5 Replies
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
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))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)RETURNIf(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.
