Forum Discussion

Txtcher's avatar
Txtcher
Helper V
1 year ago
Solved

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 al...
Need Help
  • v-aatheeque's avatar
    v-aatheeque
    1 year ago

    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!

Txtcher's avatar
Txtcher
Helper V
1 year ago

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.

IdNameAccount_Record_Type__cApplication_Status__cApplication_Type__cDate_Submitted__c
aAB8y00000009heGAARSLAN-0032993Licensing ApplicantApprovedNFA - Initialnull
aAB8y0000000BmlGAERSLAN-0000033159Licensing ApplicantApprovedNFA - Initial05/10/2024

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

IdParentIdFieldNewValueCreatedDate
0178y00001yAluSAASaAB8y00000009heGAAApplication_fee__cTRUE04/28/2022
0178y00002twVevAAEaAB8y00000009heGAAApplication_Status__cResponse Required08/31/2022
0178y00007VqzqNAARaAB8y00000009heGAAApplication_Status__cIn Review02/14/2024
0178y000085P0osAACaAB8y00000009heGAAInitiate_License_Fee__cTRUE04/21/2024
0178y000087tnKYAAYaAB8y00000009heGAAInitial_license_fee__cTRUE04/24/2024
0178y0000880wfQAAQaAB8y00000009heGAAApplication_Status__cApproved04/25/2024
0178y00003CbmRDAAZaAB8y0000000BmlGAEApplication_Status__cResponse Required10/12/2022
0178y00003CbmREAAZaAB8y0000000BmlGAEApplication_fee__cTRUE10/12/2022
0178y00003xIEshAAGaAB8y0000000BmlGAEApplication_Status__cIn Review01/20/2023
0178y00007bBeyWAASaAB8y0000000BmlGAEApplication_Status__cIn Review02/23/2024
0178y000089W0zGAASaAB8y0000000BmlGAEInitiate_License_Fee__cTRUE04/28/2024
0178y00008ISGdaAAHaAB8y0000000BmlGAEInitial_license_fee__cTRUE05/15/2024
0178y00008JA83UAATaAB8y0000000BmlGAEApplication_Status__cApproved05/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__cNameDate_payment_was_received__cFee_Type__c
aAB8y000000018oGAAFEE-000004066705/25/2022Application Fee
aAB8y00000009heGAAFEE-000002820904/14/2022Application Fee
aAB8y00000001GYGAYFEE-000004231804/28/2022License Fee
aAB8y000000092BGAQFEE-000004232605/05/2022License Fee
aAB8y0000000BmlGAEFEE-000004236908/03/2022Application Fee
aAB8y0000000BmlGAEFEE-000005351505/15/2024License Fee

 

I created my calendar table with this formula:  

FY2024 DATES = CALENDAR(DATE(2023, 9, 1),DATE(2024, 8, 31))
 
Txtcher's avatar
Txtcher
Helper V
1 year ago

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's avatar
    v-aatheeque
    Community Support
    1 year ago

    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!

    • v-aatheeque's avatar
      v-aatheeque
      Community Support
      1 year ago

      Hi Txtcher ,

      Have you resolved the issue? If yes, kindly mark the helpful answer as a solution if you feel that makes sense. Welcome to share your own solution. More people will benefit from the thread.

      Should you have any further questions, feel free to reach out.
      Thank you for being a part of the Microsoft Fabric Community Forum!