Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
bhanu_gautam
Super User
Super User

DAX Help

 
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

 

bhanu_gautam_0-1706632554316.png

 

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




Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!




LinkedIn






1 REPLY 1
Anonymous
Not applicable

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.

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.