FILTERING OUT WEEKS THAT HAVE NOT HAPPENED YET IN A DASHBOARD

[hstgeorge]

I have a data set that is based on weeks and the weeks look like the below:

2020011 = January wk1

2020012 = January wk2

2020052 = May wk2

2020121 = Dec wk1

I also have a date table that assigns the exact dates to each week - I created my own 445 calendar so I have week numbers by each date.  What I want to do is filter out all future weeks and only include closed financial weeks in the data.  So, for instance, this week is week number 40 and it is 2020101 for the recently closed week.

I want to exclude all weeks after that so exclude 2020102, 2020103, 2020104, 2020111.........and so on till you get to 2020125.

I am getting stumped on this one.  Does anyone have a suggestion?

[amitchandak]

@hstgeorge , the Model is not very clear

 forced=
var _max = maxx('Table',[Year Week])
return
if(max('Date'[Year Week])<=_max, [Measure], blank())

[hstgeorge]

Hey @amitchandak  - I will try to be a bit clearer on how my data is set up.  Each week has a number sequence like above described and the it has a column for CY sales and a column for PY sales.  What happens is in 2020121 (Week 1 December) there is sales data for 2019 (PY) but no sales data for 2020 (CY) as it has not happened yet.  this is causing a variance of -100% because I need to exclude the weeks that have not happened yet somehow as they only have PY data.  I did create a date table that has every single day of the year, it's corresponing week number (1-52).  I may be able to somehow use the date time functions even though I am working on a non-standard calendar so today would = week 41 but the week hasn't closed yet so I want to exclude all weeks >= to week 41 per the date table so only weeks 1 through 40 are selected.  I hope I am being clear I could mock up a fake data set and maybe send it over to you, but I cannot share the real data I am working with.

[v-zhenbw-msft · Accepted solution]

Hi @hstgeorge ,

We can use the IF function to meet your requirement.

last year value = 
var _lastvalue = CALCULATE(SUM('Table'[value]),SAMEPERIODLASTYEAR('Date'[Date]))
var _current = SUM('Table'[value])
return
IF(
    ISBLANK(_current),BLANK(),_lastvalue)

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that we have shared?

Best regards,

Community Support Team _ zhenbw

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

BTW, pbix as attached.