Help building a calculation or Point me in a Direction

[jsnrchtr]

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 the below formulas built to calculate different things, but what isn't working is I can't find a way to get the previous day's data. Doing some research is what prompted me to build what I did below but it is not doing what I need it to do. 

What I am trying to do is pull in my data and update my visuals. The following day I want to pull in the same data, compare it to the previous day (eventually previous week/month) and then it should adjust the trend arrow accordingly up or down or dash if no change, and change the color. I'd like to eventually graph some data using this process. What I'm trying not to do is pull data daily and store it in a folder to continually reference it if I can help it. 

KPI Previous Day Remaining Items =

VAR PrevDayValue =

    CALCULATE(

        [KPI Remaining Items],

        DATEADD('Date'[Date], -1, DAY)

    )

RETURN

    IF(ISBLANK(PrevDayValue), 0, PrevDayValue)

KPI Remaining Items =

VAR Result =

    CALCULATE(

        COUNTROWS('MMIS Status Update'),

        'MMIS Status Update'[completion_status] = "Not Completed"

    )

RETURN

    IF(ISBLANK(Result), 0, Result)

date table:

Date =

VAR MinDate = CALCULATE(MIN('MMIS Status Update'[update_date]))

VAR MaxDate = CALCULATE(MAX('MMIS Status Update'[update_date]))

RETURN

ADDCOLUMNS(

    CALENDAR(DATE(2020,1,1), DATE(2030,12,31)),

    "Year", YEAR([Date]),

    "Month Number", MONTH([Date]),

    "Month Name", FORMAT([Date], "MMMM"),

    "Year-Month", FORMAT([Date], "YYYY-MM"),

    "Quarter", "Q" & FORMAT([Date], "Q"),

    "Week Number", WEEKNUM([Date], 2), -- 2 - Week starts on Monday

    "Day of Week", FORMAT([Date], "dddd"),

    "Is Weekend", IF(WEEKDAY([Date], 2) >= 6, TRUE(), FALSE())

)

Trend Arrow:

KPI Trend Arrow =

SWITCH(

    TRUE(),

    [KPI Remaining Difference] <0,UNICHAR(11165), // Down arrow

    [KPI Remaining Difference] >0,UNICHAR(11167), // Up arrow

    UNICHAR(9644)                                // Dash for no change

)

Trend Arrow Color:

KPI Trend Arrow Color =

SWITCH(

    TRUE(),

    [KPI Remaining Difference] >0, "Green",

    [KPI Remaining Difference] <0, "Red",

    "Gray"

)

KPI Remaining Difference =

[KPI Remaining Items] - [KPI Previous Day Remaining Items]

[jsnrchtr]

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.

lin	NIINMCN	model	serial_number	completion_status	req_compl_end_date	completion_date	update_date
E05011	011234567	A	1	Completed	11/9/2024	8/14/2024	8/14/2024
E05011	011234567	A	2	Completed	7/18/2025	2/26/2025	2/26/2025
E05011	011234567	A	3	Completed	8/21/2024	11/15/2022	11/16/2022
E05011	011234567	A	4	Completed	11/9/2024	10/3/2024	10/3/2024
E05011	011234567	A	5	Completed	7/18/2025	4/1/2025	4/23/2025
E05011	011234567	A	6	Completed	8/21/2024	5/20/2021	5/24/2021
E05005	013217654	B	7	Completed	7/18/2025	4/2/2025	4/2/2025
E05004	019874321	C	8	N/A	2/16/2021		2/17/2021
E05005	013217654	B	10	Not Completed	7/18/2025		2/14/2025
E05004	019874321	C	11	Completed	11/9/2024	12/16/2024	12/16/2024
E05004	019874321	C	12	Completed	11/9/2024	8/26/2024	9/4/2024
E05004	019874321	C	13	Completed	11/9/2024	11/5/2024	11/5/2024
E05004	019874321	C	14	Completed	2/27/2021	5/5/2021	5/5/2021
E05004	019874321	C	15	Completed	8/21/2024	12/13/2021	1/25/2022
E05004	019874321	C	16	Completed	11/9/2024	10/4/2024	10/10/2024
E05004	019874321	C	17	N/A	2/16/2021		2/17/2021
E05004	019874321	C	18	Completed	11/9/2024	10/17/2024	10/17/2024
E05004	019874321	C	19	Completed	8/21/2024	6/8/2021	6/8/2021
E05004	019874321	C	9	Completed	8/21/2024	6/8/2021	6/8/2021

[v-dineshya]

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]

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]

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]

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]

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.

[v-dineshya]

Hi @jsnrchtr ,

As per your requirement, i have updated the code.

KPI Remaining Items (Today) =

CALCULATE (

    COUNTROWS ( 'MMIS Status Update' ),

    'MMIS Status Update'[completion_status] = "Not Completed"

)

KPI Previous Day Remaining Items =

VAR PrevDate =

    CALCULATE (

        MAX ( 'Date'[Date] ),

        FILTER ( 'Date', 'Date'[Date] = TODAY () - 1 )

    )

RETURN

    IF (

        ISBLANK (PrevDate),

        0,

        CALCULATE (

            COUNTROWS ( 'MMIS Status Update' ),

            'MMIS Status Update'[completion_status] = "Not Completed",

            'Date'[Date] = PrevDate

        )

    )

KPI Trend Arrow Color =

SWITCH (

    TRUE(),

    [KPI Remaining Difference] < 0, "Green",

    [KPI Remaining Difference] > 0, "Red",

    "Gray"

)

Please refer the output snap and attached PBIX file.

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]

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.

[v-dineshya]

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]

Adjusted the formulas as supplied. Made one change for the arrows... > 0 Green , < 0 Red.

All arrows are still moving at the same time and still seem to only be refreshing the first time data comes in. When the dashboard refreshes, the arrows ignore the new data. I'll verify again tomorrow morning when the dashboard will have new data changes (The source only updates once daily and I don't control the source). I am still also getting strange slicer behavior. If I click on a single unit, my completion percentage card reflects odd numbers. Total number is accurate. If i filter by a unit, that completion percentage instead of showing that units completion percentage, ballons to over 200% in some cases. 

[v-dineshya]

Hi @jsnrchtr ,

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

Thanks

[v-dineshya]

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.

[v-dineshya]

Hi @jsnrchtr ,

Thank you for reaching out to the Microsoft Community Forum.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
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.

Thank you