DAX Help

Hi bhanu_gautam

For your question, here is the method I provided.

To create a dynamic line chart that shows the distinct count of Tour Request numbers every week for the current termination year and where the Qt Exclude Flag is '0', and that dynamically updates to show data for the current week and the past 6 weeks upon refresh, please follow the steps below:

First, we need a measure to calculate the distinct count of Tour Request numbers where the Termination Year is the current year and the Qt Exclude Flag is '0'.

Distinct Tour Requests = 
    CALCULATE(
        DISTINCTCOUNT('Table'[Tour Request NO]), 
        FILTER(
            ALL('Table'), 
            'Table'[ Qt ] = 0 
            && 
            'Table'[weeknum] = MAX('Table'[weeknum]) 
            && 
            'Table'[Termination year] = YEAR(TODAY())
        )
    )

Create a Measure for the Current Week.

Current Week = WEEKNUM(TODAY(), 2)

Create a Measure for Filtering the Last 7 Weeks.

Show Last 7 Weeks = 
IF(
   
      'Table'[Week number] <= [Current Week]
      &&
      'Table'[Week number] > [Current Week] - 7
   ),
   1,
   0
)

Create Your Line Chart:

Add a line chart to your report.

Set the axis to use the 'Week number' from your table.

Use the 'Distinct Tour Requests' measure as the Value.

Add a visual-level filter to the chart using the 'Show Last 7 Weeks' measure, and filter by the value "1" to include only data for the desired weeks.

Make sure your Power BI dataset is refreshed regularly so that the line chart updates dynamically as time progresses.

If you're still having problems, provide some dummy data and the desired outcome. It is best presented in the form of a table.

Regards,

Nono Chen

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