DAX Help

[bhanu_gautam]

I have week number column and Termination year column and Tour Request no column and Qt Exclude Flag column in a table in power bi desktop. I have a week column from 1 to 52 week numbers. I want to show the distinct count of Tour Request no of Every week of Termination Year Current year and Qt Exclude Flag '0' individually up to current week in the line chart .and the Current week to Past 6 weeks count should be Dynamically change weekly when I refresh the report

Like this. for example my currentweek is 48, the linechart should show the current week and Last 6 weeks Data for individual weeks.

[v-nuoc-msft]

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.