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

[Joachim2108]

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.

[v-stephen-msft · Accepted solution]

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.

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.

[Joachim2108]

Thanks a lot Stephen. Works perfectly.

[jdbuchanan71]

@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/

[Joachim2108]

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.

[amitchandak]

@Joachim2108 , Change you last is measure like

last year new =

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