Forum Discussion

jsnrchtr's avatar
jsnrchtr
Regular Visitor
1 year ago
Solved

Help building a calculation or Point me in a Direction

I have a dataflow that is sourcing from an online dataset that updates daily. My dashboard updates daily and pulls in each of probably 4 or 5 different datasets all updating the same way.    I have...
Need Help
  • v-dineshya's avatar
    v-dineshya
    1 year ago

    Hi jsnrchtr ,

    Please provide sample PBIX file and show the expected outcome based on the sample data .

     

    Thanks

jsnrchtr's avatar
jsnrchtr
Regular Visitor
1 year ago

In the photo where the gray dashes are, those are the "trend arrow" calculations. The number to the left as it changes, I am wanting the dash to reflect its change. Example: If the previous day was 10, and today it is 9, then I should see a green down arrow. If the number is now 12, I should see a red up arrow. If there is no change, the gray dash should remain. It should reference the previous day's data as the dashboard refreshes.

 

linNIINMCNmodelserial_numbercompletion_statusreq_compl_end_datecompletion_dateupdate_date
E05011011234567A1Completed11/9/20248/14/20248/14/2024
E05011011234567A2Completed7/18/20252/26/20252/26/2025
E05011011234567A3Completed8/21/202411/15/202211/16/2022
E05011011234567A4Completed11/9/202410/3/202410/3/2024
E05011011234567A5Completed7/18/20254/1/20254/23/2025
E05011011234567A6Completed8/21/20245/20/20215/24/2021
E05005013217654B7Completed7/18/20254/2/20254/2/2025
E05004019874321C8N/A2/16/2021 2/17/2021
E05005013217654B10Not Completed7/18/2025 2/14/2025
E05004019874321C11Completed11/9/202412/16/202412/16/2024
E05004019874321C12Completed11/9/20248/26/20249/4/2024
E05004019874321C13Completed11/9/202411/5/202411/5/2024
E05004019874321C14Completed2/27/20215/5/20215/5/2021
E05004019874321C15Completed8/21/202412/13/20211/25/2022
E05004019874321C16Completed11/9/202410/4/202410/10/2024
E05004019874321C17N/A2/16/2021 2/17/2021
E05004019874321C18Completed11/9/202410/17/202410/17/2024
E05004019874321C19Completed8/21/20246/8/20216/8/2021
E05004019874321C9Completed8/21/20246/8/2021

6/8/2021

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

    Hi jsnrchtr ,
    Thank you for reaching out to the Microsoft Fabric Community forum.

     

    Please follow below steps.

     

    1. please do change your code with below things.

    Make 

    sure your 'Date' table is related to 'MMIS Status Update'[update_date].

    2. Make sure you are filtering the data by a single date (e.g., today), either by a slicer or a measure logic using TODAY().

    3. Current Day KPI Remaining Items

    KPI Remaining Items =
    CALCULATE(
    COUNTROWS('MMIS Status Update'),
    'MMIS Status Update'[completion_status] = "Not Completed"
    )

    4. Previous Day KPI Remaining Items

    KPI Previous Day Remaining Items =
    CALCULATE(
    [KPI Remaining Items],
    DATEADD('Date'[Date], -1, DAY)
    )

    Note: Make sure 'Date'[Date] is marked as a Date table and has a relationship to 'MMIS Status Update'[update_date].

    5.
    KPI Remaining Difference = [KPI Remaining Items] - [KPI Previous Day Remaining Items]

    KPI Trend Arrow =
    SWITCH(
    TRUE(),
    [KPI Remaining Difference] < 0, UNICHAR(11165),
    [KPI Remaining Difference] > 0, UNICHAR(11167),
    UNICHAR(9644)
    )

    KPI Trend Arrow Color =
    SWITCH(
    TRUE(),
    [KPI Remaining Difference] > 0, "Red",
    [KPI Remaining Difference] < 0, "Green",
    "Gray"
    )

     

    If this information is helpful, please “Accept it as a solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
    Thank you.

    • jsnrchtr's avatar
      jsnrchtr
      Regular Visitor
      1 year ago

      I had the formulas recommended above already in place initially... the issue that gave me was when the quantity remaining was 0, it returned [blank] instead of "0". I needed it to say 0 instead. 

       

      Can you provide an example how to filter measure logic using today() ? I understand what you're saying, but not sure where to put that reference. 

       

      I have a today() calc to determine if the requirement is overdue: 

      MMIS OVERDUE = IF('MMIS Status Update'[req_compl_end_date].[Date] <=TODAY () , "YES", "NO")
       
      I'm not sure how to apply it for the normal calculations.
       
      • v-dineshya's avatar
        v-dineshya
        Community Support
        1 year ago

        Hi jsnrchtr ,

         

        Please refer the attached PBIX file for your reference.

         

        If this information is helpful, please “Accept it as a solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
        Thank you.