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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Joachim2108
Helper I
Helper I

Comparing number of sales this year and last year, and only show data until todays date.

Hi all,

 

I've created a visual comparing last years sales with sales from this year, with week number on the X axis. My issue is that PBI includes all weeks from last year, as you can see on the image. What I want it to do, is only show the data up until today/as far as the blue columns go on the chart.

 

The week numer comes from my date table and the values are just taken from a field in the sales table, no measures have been used.

 

Joachim2108_0-1646317509259.png

 

1 ACCEPTED SOLUTION
v-stephen-msft
Community Support
Community Support

Hi @Joachim2108 ,

 

Do you have a calendar table with date, year and week columns?

You may add a visual-level filter. Here's the solution.

Create a measure.

Measure = IF(WEEKNUM(TODAY())>=WEEKNUM(MAX('Calendar'[Date])),1)

Add the measure in to the visual level filters and set show items when the value is 1.

4.png

 

You can check more details from my attachment.

 

 

Best Regards,

Stephen Tao

 

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

View solution in original post

5 REPLIES 5
v-stephen-msft
Community Support
Community Support

Hi @Joachim2108 ,

 

Do you have a calendar table with date, year and week columns?

You may add a visual-level filter. Here's the solution.

Create a measure.

Measure = IF(WEEKNUM(TODAY())>=WEEKNUM(MAX('Calendar'[Date])),1)

Add the measure in to the visual level filters and set show items when the value is 1.

4.png

 

You can check more details from my attachment.

 

 

Best Regards,

Stephen Tao

 

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

Thanks a lot Stephen. Works perfectly.

jdbuchanan71
Super User
Super User

@Joachim2108 

Take a look at this article that goes through how to hide the extra dates.

https://www.sqlbi.com/articles/hiding-future-dates-for-calculations-in-dax/

 

Unfortunately this only works if you're trying to show YTD figures/cumulative values. I only need to show the sales figures for each week up until this point, but thanks for the quick reply.

@Joachim2108 , Change you last is measure like

 

last year new =

if(isblank([This year]), blank() , [Last year])

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.