Measure Value in Range of Time

[Anonymous]

Hi Everyone.

I have two table name Date table and fact table. I have date slicer that is using Date Table. Date table is connected with startdate column. I would like to display the Average of column score in fact table if the slicer selection is between column startdate and enddate. Anyone have idea how to do this?

Thank you!

[Johnshahsmith]

o achieve this in Power BI, ensure your Date table is related to the Fact table via the startdate column. Then, use a measure to calculate the average score where the slicer selection is between the startdate and enddate.

1. First, create a measure to filter the Fact table based on the slicer selection using CALCULATE and FILTER. The measure would look something like this:
   DAX

   Copy code

   AverageScore = CALCULATE( AVERAGE(FactTable[Score]), FactTable[StartDate] <= MAX(DateTable[Date]), FactTable[EndDate] >= MIN(DateTable[Date]) )

   1. The MIN and MAX of the Date slicer define the selected date range. The CALCULATE function ensures the average is computed only for rows where the startdate and enddate fall within the slicer's range.

      Finally, add this measure to your visual, and it will reflect the correct average based on the slicer selection.

[ghazi12]

A business school provides education in business administration and management, offering programs like MBAs and specialized degrees to develop skills in finance, marketing, strategy, and leadership for professional advancement.

[ghazi12]

A dental school trains future dentists through comprehensive programs in oral health, clinical practice, and dental science, leading to a DDS or DMD degree, essential for a career in dentistry.

[ghazi12]

A medical school educates future doctors, offering rigorous training in medical science, clinical practice, and patient care, leading to an MD or DO degree, essential for a career in medicine.

[ghazi12]

A medical school educates future doctors, offering rigorous training in medical science, clinical practice, and patient care, leading to an MD or DO degree, essential for a career in medicine.

[ghazi12]

A medical school educates future doctors, offering rigorous training in medical science, clinical practice, and patient care, leading to an MD or DO degree, essential for a career in medicine.

[mahnoor1]

Hi,

To achieve this, you can use a DAX measure in Power BI that calculates the average of the score column in your fact table, taking into account the date slicer that is based on the Date table and filtering between the startdate and enddate columns. Here’s a step-by-step guide on how to create this measure:

1. Ensure Relationships: Verify that your Date table is correctly related to your fact table through the startdate column.

2. Create the Measure: Use the following DAX code to create a measure that calculates the average score within the specified date range.

   DAX

   Copy code

   AverageScoreInRange = VAR SelectedStartDate = MIN('Date Table'[Date]) VAR SelectedEndDate = MAX('Date Table'[Date]) RETURN CALCULATE( AVERAGE('Fact Table'[score]), 'Fact Table'[startdate] >= SelectedStartDate, 'Fact Table'[enddate] <= SelectedEndDate )

   1. Add the Measure to Your Report: Add this new measure to your report to see the average score based on the selected date range in your slicer.

      Explanation:

      • SelectedStartDate and SelectedEndDate capture the minimum and maximum dates selected in the slicer.
      • CALCULATE is used to modify the context of the calculation. It filters the Fact Table to only include rows where startdate is greater than or equal to the selected start date and enddate is less than or equal to the selected end date.
      • AVERAGE calculates the average of the score column within the filtered context.

        Additional Tips:

        • Ensure your Date table contains a continuous range of dates that cover the possible values in the startdate and enddate columns.
        • Validate that your slicer is connected and affecting the Date table as expected.
        • Check that there are no missing or incorrect relationships between tables that might affect the calculations.

          Feel free to ask on Yacine TV if you have any more questions or need further assistance!

          Thank you!

[harsh123]

To achieve this, you can create a measure in Power BI using DAX. First, create a measure for the average score in your fact table. Then, use the FILTER function to filter the fact table based on the selected date range from the slicer url using RELATED function to relate the date column in the fact table to the date column in the date table. Finally, calculate the average score within this filtered context. Your measure would look something like this:

css

Copy code

AverageScoreInRange = AVERAGEX( FILTER( FactTable, FactTable[StartDate] <= MAX(DateTable[Date]) && FactTable[EndDate] >= MIN(DateTable[Date]) ), FactTable[Score] )

This measure will dynamically calculate the average score based on the selected date range from the slicer.

[Syndicate_Admin]

Hello! It looks like you're looking to perform a filtering and averaging operation based on date segmentation. Here's a general approach you could take, assuming you're working with some query language or data manipulation like SQL.

Let's say you have two tables: DateTable, and FactsTable. Here's an example of how you might structure your query:

sqlCopy code

SELECT AVG(hecho_columna) AS AverageScore FROM TableFacts WHERE fecha_inicio >= SelectedDate AND fecha_finalizacion <= SelectedDate;

In this consultation:

• AVG(hecho_columna) calculates the average of the column of facts you're interested in.
• WHERE fecha_inicio >= SelectedDate AND fecha_finalizacion <= SelectedDate filters the records in the fact table based on the date selected in the date slicer.

  Be sure to replace SelectedDate with the actual variable or value you get from the date slicer.

  This is a general example and you may need to adjust the GB WhatsApp syntax depending on the database management system you're using and the exact structure of your tables. If you're using a different environment or if you have more details about your setup, it might provide a more specific answer. GB WhatsApp APK

[Ashish_Mathur]

Hi,

Based on that data that you have shared, show the expected result.

[Syndicate_Admin]

Hello

You can use the following SQL query to get the average of the fact column based on date targeting:

sql

Copy code

SELECT AVG(h.hecho_columna) AS PromedioPuntuacion FROM tabla_de_hechos h JOIN tabla_de_fechas d ON d.fecha = h.fecha_inicio WHERE d.fecha BETWEEN h.fecha_inicio AND h.fecha_finalizacion;

Here:

• AVG(h.hecho_columna) calculates the average of hecho_columna.
• JOIN tabla_de_fechas d ON d.date = h.fecha_inicio connects the tables.
• WHERE d.date BETWEEN h.fecha_inicio AND h.fecha_finalizacion filters records based on date selection.

  Adjust according to your specific DBMS and table structure. Install AN WhatsApp Now

[Anonymous]

sorry, there additional column name 'project' as below;

Example if project is A and date selection is May - 23 (within startdate 7 enddate) then 4, if project is A and date selection is Jan - 23(outside startdate & enddate) then Blank

[VahidDM · Accepted solution]

Hi @Anonymous 

Try this measure:

Avg. Score Selected Date = 
Var _Max_Selected_Date = MAX('Date table'[Date])
Var _Filtered_Fact_Table = 
    CALCULATETABLE(
        FILTER(
        'Fact Table',
        'Fact Table'[StartDate]<=_Max_Selected_Date&&'Fact Table'[EndDate]>=_Max_Selected_Date
        ),
        CROSSFILTER('Date table'[Date],'Fact Table'[StartDate],None)
    )
RETURN
AVERAGEX(_Filtered_Fact_Table,[Score])

Output:

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!! 

LinkedIn | Twitter | Blog | YouTube 

[Anonymous]

Thank you very much. This is the answer I'm looking for 🙂