Power BI CQD using DAX for Power Automate - Average of value by time

[Heinrich]

Hello

I want to use values from Microsoft CQD (direct query) and have hourly (2 or 4 hours) average of a values.

But the DAX used has absolute numbers (dates, time). So no possibility copying this in Power Automate.

It should be relative. 

Do you have a solution. 

Thank you very much.

Heinrich

[Heinrich]

Hello @v-sgandrathi 
Thanks for your reply
Some values which I need are empty and so I had to copy all values into a new table.
But this table is not charged fully and filtering does not work. The reason why I copy the data to a local table is 
because there are some values which are empty.
Regards
Heinrich

[v-sgandrathi]

Hi @Heinrich,

Thankyou @burakkaragoz for sharing this, your solution using NOW() and a dynamic time window in DAX is correct and ideal for Microsoft CQD Direct Query scenarios. This avoids hardcoded timestamps and calculates rolling averages dynamically, working well with Power Automate alerts.

Use a DAX measure with NOW() to define a dynamic time window (like the last 2 or 4 hours) and calculate the average within that period using CALCULATE and FILTER. Avoid calculated columns with fixed timestamps since they won’t update dynamically in Direct Query. This way, your average stays relative and works well with Power Automate.

If this solution worked for you, kindly mark it as Accept as Solution and feel free to give a Kudos, it would be much appreciated!

We're happy to assist you on the Microsoft Fabric Community Forum.

[Heinrich]

Hello @v-sgandrathi 
Thank you very much for your support. 
Have a great day

Heinrich

[v-sgandrathi · Accepted solution]

Hi @Heinrich,

As we have not received a response from you yet, I would like to confirm whether you have successfully resolved the issue or if you require further assistance.

If the issue has been resolved, please mark the helpful reply as a "solution" to indicate that the question has been answered and to assist others in the community.

Thank you for your cooperation. Have a great day.

[Heinrich]

Hello @v-sgandrathi 
Thank you for your question. I was away for the weekend.

Will have a look today.

Regards and have a great day

[Heinrich]

Hello @v-sgandrathi 
Hello @burakkaragoz 
I had a look and the table is not loaded fully.
I will try and analyze it.
Will close this one. Thank you all for your help.
Regards
Heinrich

[saritasw]

@Heinrich ,
You can try creating a calculated column that groups the time into 2-hour or 4-hour buckets using DATEDIFF and NOW() to keep it relative. Then create a measure lto get the average value per bucket.
This way, your data stays dynamic and works with Power Automate without hardcoded dates.

Hope that helps!
------------------------------
If this solution worked for you, kindly mark it as Accept as Solution. This would be helpful for other members who may encounter similar issues and feel free to give a Kudos, it would be much appreciated!

Thank you,
Sarita 

[burakkaragoz]

Hi @Heinrich ,

Yeah, using absolute timestamps in DAX can be tricky when you want to automate stuff in Power Automate. One way to make it more dynamic is to use relative time logic in your DAX, like filtering based on NOW() or TODAY() and then grouping by hour ranges.

You could try something like this to get average over the last 2 or 4 hours:

VAR CurrentTime = NOW()
VAR StartTime = CurrentTime - TIME(4,0,0) -- for 4 hours
RETURN
CALCULATE(
    AVERAGE('YourTable'[YourValue]),
    FILTER(
        'YourTable',
        'YourTable'[Timestamp] >= StartTime &&
        'YourTable'[Timestamp] <= CurrentTime
    )
)

This way, you avoid hardcoding dates and it should work better with Power Automate flows.

Let me know if you need help tweaking it for your exact use case.

If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.
translation and formatting supported by AI

[Heinrich]

Hello @burakkaragoz 
Great solution.

I immagine this is a measure and has to be inserted into the Direct Query Table, right?

Did I understand it right:

1. Take Visual: Table

2. Columns:

    - Column: SBC

    - Column: NER

3. Filter on this page:

    - Measure that you did explain

Is this the goal? Will NER be calculated by x hours?
Regards and have a great day
Heinrich

[burakkaragoz]

Hi @Heinrich ,

Glad it helped!

Yes, you're right. The DAX I shared is meant to be used as a measure. Since you're using DirectQuery, it’ll calculate dynamically based on the current time.

To clarify:

1. Use a Table or Matrix visual.
2. Add your columns like SDC, NER, etc.
3. Create the measure with the relative time logic.
4. You can either:
   • Add the measure directly to the visual, or
   • Use it as a filter to only show data from the last 2 or 4 hours.

If you want to group values into 2-hour blocks (like 00:00–02:00, 02:00–04:00), we can also build a calculated column for that. Just let me know and I’ll help with the logic.
translation and formatting supported by AI

[Heinrich]

Hello @burakkaragoz 
Great work.
I tried it but DAX did not find the table or values

Regards

Heinrich

[burakkaragoz]

Hi @Heinrich ,

Thanks for sharing the DAX. The logic looks solid, but the error you're seeing usually means that either:

1. The table 'CDQ' doesn’t exist in your model, or
2. The column names 'StartTime' or 'PSTN NER Good Percentage' are not matching exactly (case-sensitive, spacing matters)

Here’s what you can do:

• Open the Fields pane in Power BI and double-check the exact names of the table and columns.
• Make sure there are no extra spaces or different casing (e.g. Start Time vs StartTime)
• Once confirmed, update the DAX like this:

VAR CurrentTime = NOW()
VAR StartTime = CurrentTime - TIME(4,0,0)
RETURN
CALCULATE(
    AVERAGE('YourTableName'[YourColumnName]),
    FILTER(
        'YourTableName',
        'YourTableName'[StartTimeColumn] >= StartTime &&
        'YourTableName'[StartTimeColumn] <= CurrentTime
    )
)

Replace 'YourTableName', 'YourColumnName', and 'StartTimeColumn' with the exact names from your model.
translation and formatting supported by AI

[Heinrich]

Hello @burakkaragoz 

I did it the measure worked.

But I got then an Error

I added it once as filter on the visual and then as second try added to the visual.

Regards

Dagoxx

[Heinrich]

I think I might have a reason

1. there are cells that do not have a value and this might have an effect

2. If I can use it as a filter but the value is set on the measure than this will overwrite the value set on the filter right?

[v-sgandrathi]

Hi @Heinrich,

Yes, you're thinking in the right direction; both of those points can definitely impact how your DAX measure behaves.

If the column (e.g., NER) contains blank values, AVERAGE() will ignore them. However, if all values in the filtered range are blank, the result will also be blank, which can make visuals appear empty or misleading.

When a measure includes built-in filters (like NOW() or a time range), those override slicers or visual filters. So, if you filter by time externally and also have time logic inside the measure, it may lead to conflicts or no data being shown.

Use the measure directly in the visual (like a table or card) and control the time filtering separately (example: wiith slicers or page filters) to avoid conflicts.

I hope my suggestions provided valuable insights. If you have any further questions, don’t hesitate to ask in a follow-up message.
If this post helped, please mark it as "Accept as Solution" so others can benefit as well.

Best regards,
Sahasra.

[v-sgandrathi]

HI @Heinrich,

I wanted to follow up on our previous suggestions regarding the issue. We would love to hear back from you to ensure we can assist you further.

If our response has addressed your query, please accept it as a solution and give a ‘Kudos’ so other members can easily find it. Please let us know if there’s anything else we can do to help.

Thank you.

[Heinrich]

Hello @v-sgandrathi 

Hope you had a great weekend. Sorry for not replying.
I did a workaround by mirroring the data to an internal table and replaced the empty ones with values that can not distort the result.
The problem for that is that it is limitting the import. Do you have an Idea how to filter or load only the last x months?
Regards and have a great day
Heinrich

[v-sgandrathi]

Hi @Heinrich,

Thank you for being a part of the Microsoft Fabric Community.

To limit the data to only the last few months in Power BI when using DirectQuery (like with CQD), you can apply a relative date filter directly in the report. Just use the date or timestamp column in the visual or page filters and set it to “in the last X months” (e.g., 3 or 6 months). This helps reduce the data being queried, which improves performance and avoids loading unnecessary records. Alternatively, if you're using a measure, you can include date logic inside it using TODAY() and EOMONTH() functions to filter the data dynamically. For example, define a start date as 3 months before today and use it in a CALCULATE statement with FILTER to only include recent records. If you're importing mirrored data into an internal table, apply a filter step in Power Query to load only rows with dates from the last few months. These approaches ensure you're only working with relevant and recent data without affecting your Power BI or Power Automate performance.

If my response was helpful, consider clicking "Accept as Solution" and give us "Kudos" so that other community members can find it easily. Let me know if you need any more assistance!

Regards,
Sahasra.