Power BI - Refresh Data

[EricMencarini]

Hello to everyone! Good evening!

I would appreciate so much if anyone could help me with this:

Context:
For everytime that my dashboard have a 'refresh', I have a card that show the date dd/mmmm/yyyy hh:ss:nn for the last refresh.

Measure = 
MAXX(
    {
        MAX(table[column]),
        MAX(table[column])
    },
    [Value]
)

Whcih returns this for example:  23/05/2024 15:23:33 (Latest refresh)
 
But what I want to do is:

(btw I did a lot of research but I couldn't find a solution to it[Youtube, chat gpt, forums])
I just want this measure to be refreshed if the data in Power Bi really changed

What I was thinking is to storage any metric value in the dashboard, and then after the refresh to make a comparison

Example:
Latest refresh : 23/05/2024 15:23:33
Clients = 842

After dashboard refresh:
If Clients > 842 then we get the new date: 24/05/2024 15:23:33
else (Clients = 842 it keeps the date from latest refresh: 23/05/2024 15:23:33

If anyone have any ideas I would appreciate a lot!
Thank you!

[danextian · Accepted solution]

Here's what I am thinking.
In the query editor, create a query that updates every time a semantic model refreshes. This query contains the refresh datetime.

=DateTimeZone.SwitchZone( DateTimeZone.LocalNow(), 😎 // the service uses UTC this needs to converted to your local timezone. 8 = UTC+8. This query will always be a single row.

In Power Automate, run a query against a dataset.  The query should pickup the refresh datetime from the semantic model + the count of the client. You can simulate a query by creating a CALCULATED table inside Power BI and then transfer the logic to Power Automate. The syntax is similar to that of when using DAX Query or DAX Studio. Here's a sample:

DEFINE
    VAR __MAX =
        MAX ( MaxDateFile[Date] )
    VAR __MAXFILE_DU =
        CALCULATE (
            MAX ( MaxDateFile[Filename] ),
            FILTER (
                MaxDateFile,
                MaxDateFile[Date] = __MAX
                    && MaxDateFile[Type] = "DU"
            )
        )
    VAR __MAXFILE_PB =
        CALCULATE (
            MAX ( MaxDateFile[Filename] ),
            FILTER (
                MaxDateFile,
                MaxDateFile[Date] = __MAX
                    && MaxDateFile[Type] = "PB"
            )
        )
    VAR __RESULT =
        ROW (
            "Max Date", __MAX,
            "Max File DU", __MAXFILE_DU,
            "Max File PB", __MAXFILE_PB
        )

EVALUATE
UNION (
    ROW ( "Category", "Max Date:", "Value", FORMAT ( __MAX, "DD MMM YYYY" ) ),
    ROW ( "Category", "DU File:", "Value", __MAXFILE_DU ),
    ROW ( "Category", "PB File:", "Value", __MAXFILE_PB )
)

Use DEFINE when defining variables. Use EVALUATE instead of RETURN.

Append the output to an Excel online table. Connect to the Excel file in Power BI. Load it to the model. Compare the latest count in the Excel file to that of current data.

The gist here is that you must be able to store a snapshot of your data somewhere to be able to make a comparison between the previous state of the data and the current.

Disclaimer: I am no Power Automate expert but I checked on the connectors and it seems my proposition is possible.

[EricMencarini]

I was just thinking something like this, but you make it clear, thank you!!

[danextian]

This pbix might help once you get the tables set up. The logic inside compares the current against the previous state. In the  screenshot below, the current refresh count is 104 which is the same as the previous refresh. The logic will pickup not the latest but the earliest datetime with the same count just after the count has changed to 104.

[EricMencarini]

Even with a solution outside of Power BI? 
Like Power automate or something related?

[Anonymous]

Hi @EricMencarini ,

Sorry, so far, to my knowledge, this may not be achieveable.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.