Problem on DAX average of

lifesafari · ‎09-18-2023

Hello i have a db connect with some columns. One of this is pcsMin, the quantity of pieces count by telemetry on each minute (for example 16/09/2023 10.01.00 -> 5 / 16/09/2013 10.02.00 -> 10). I need to create a DAX to have the average piece par minutes for a week.

I've create this DAX but if i calculate the average manually the resutl is different..where i wrong?

Average Pieces Last Week =

VAR LastWeekStartDate = TODAY() - WEEKDAY(TODAY(), 1) - 7 //sette

VAR LastWeekEndDate = LastWeekStartDate + 6

RETURN

AVERAGEX(

FILTER(

ICT_I4_Cont,

ICT_I4_Cont[dateStamp] >= LastWeekStartDate && ICT_I4_Cont[dateStamp] <= LastWeekEndDate

),

ICT_I4_Cont[pcsMin]

)

123abc · ‎09-18-2023

The DAX formula you've provided seems correct for calculating the average pieces per minute for the last week. However, there might be some considerations to ensure the calculation is accurate:

1. **Data Integrity:** First, make sure that your data is complete and accurate. Double-check that there are no missing or duplicate records in your data set.

2. **Date and Time Format:** Verify that the `dateStamp` column is in the correct date and time format. It should be a datetime data type, and the time component should include seconds. Your DAX code assumes that the `dateStamp` column contains both date and time information.

3. **Time Zone:** Be aware of the time zone of your data and your system. Ensure that the time zone is consistent to avoid discrepancies in your calculations.

4. **Data Filtering:** Your DAX code uses the `FILTER` function to filter the data for the last week. Ensure that the filter criteria match your expectations and that the relevant data is being included in the calculation.

5. **Manual Calculation:** When comparing the result with manual calculations, make sure you're using the same data and applying the same date and time filters.

6. **Granularity:** Confirm that your data has the granularity you expect. For example, if you're calculating the average pieces per minute, ensure that your data has records for every minute of each day.

7. **Data Model:** Check if there are any interactions or filters in your Power BI report that might affect the calculation. The DAX code you provided should work independently of other visuals, but interactions can sometimes cause unexpected results.

8. **Visual Display Format:** Ensure that the visual displaying the average pieces is using the correct format. If it's not displaying as expected, check the formatting settings in the Power BI visual.

If after verifying these considerations, you still encounter discrepancies, it may be helpful to provide more details about your data and the specific results you're getting. Additionally, you can use the DAX Studio tool to debug and evaluate your DAX calculations for more insight into the calculation process and any potential issues.

lifesafari · ‎09-18-2023

Hello and thanks for reply.
Yes the colum with date and time have type "date and time". There are any duplicate data and with manually calc i tax the sum of colum "pcsMin" and divide with number of row. And the other point..yes i've check that.

some_bih · ‎09-18-2023

Hi @lifesafari "duplicate data " is your issue mainly.

Still, I guess you have Calendar / Date table with

- year &

- week ID column

Put on visual your average measure (obviusly not from your example above) and put these columns from Calend / Date table and your results should be plausibale (Year and week ID guarantee unique values).

Hope this help, kudos appreciated.

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

lifesafari · ‎09-18-2023

Hello @some_bih and thank to your replay. No, i not have a calendar table. This is a db with daily update..are there any problem with this? So ...wich is the best practice to create a calendar table for my job?