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

v-dineshya's avatar
v-dineshya
Icon for Community Support rankCommunity 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
    Icon for Community Support rankCommunity 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.

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

      Hi jsnrchtr ,

      We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet.do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

       

      Thank you.

      • jsnrchtr's avatar
        jsnrchtr
        Regular Visitor
        1 year ago

        The arrows are moving, but I'm waiting on the data to change again to confirm. I should know tomorrow as I just updated data that will reflect tomorrow in the cloud. The issue I'm having though is the arrows (with the exception of the one item that is "0" and a dash) are all changing at the same time. If Unit A goes down by 1, all unit arrows reflect the change vs. just that unit. I have each card filtered by unit but it is as if its ignoring the filter. I'm having that issue with a few cards on the dashboard I think and I'm not sure why it is choosing to filter some cards but not all cards.