How to get the weeks data from values selected by two slicer?

[nardtmo]

Hi All,

I have this table below and also have date table with dates / weeks / months. 

I want my user to select a date from slicer 1 and then will calculate sum previous week data 

example if i select 12/28/2020 which wk num 53... sum of fruits will be from 12/20-12/26 or WK 52  and this will be my pre values

and 

If user select a date from slicer 2 it will then calculate sum of a week after the selected date/week

example if (slicer 2) select 1/5/2021 which is wk num 2... sum of fruits will be from 1/10/21-1/16/21 or WK 3 and this will be my post values

tried doing this but not getting the results.

pre date = CALCULATE([fruits],all('fact'[Period start time]),userelationship('ComparisonDate'[Date],''fact'[Period start time]))

thanks in advance. Appreciate the help.

Period start time	Fruits
12/13/2020 0:00	7
12/14/2020 0:00	2
12/15/2020 0:00	7
12/16/2020 0:00	10
12/17/2020 0:00	5
12/18/2020 0:00	10
12/19/2020 0:00	5
12/20/2020 0:00	7
12/21/2020 0:00	9
12/22/2020 0:00	3
12/23/2020 0:00	5
12/24/2020 0:00	5
12/25/2020 0:00	7
12/26/2020 0:00	8
12/27/2020 0:00	5
12/28/2020 0:00	7
12/29/2020 0:00	6
12/30/2020 0:00	8
12/31/2020 0:00	4
1/1/2021 0:00	5
1/2/2021 0:00	11
1/3/2021 0:00	3
1/4/2021 0:00	6
1/5/2021 0:00	10
1/6/2021 0:00	4
1/7/2021 0:00	20
1/8/2021 0:00	3
1/9/2021 0:00	4
1/10/2021 0:00	4
1/11/2021 0:00	130
1/12/2021 0:00	72
1/13/2021 0:00	5
1/14/2021 0:00	6
1/15/2021 0:00	2
1/16/2021 0:00	5
1/17/2021 0:00	1
1/18/2021 0:00	4
1/19/2021 0:00	7
1/20/2021 0:00	24
1/21/2021 0:00	5
1/22/2021 0:00	12
1/23/2021 0:00	4
1/24/2021 0:00	4
1/25/2021 0:00	5
1/26/2021 0:00	10

[v-henryk-mstf · Accepted solution]

Hi @nardtmo ,

According to the trial data you provided. I made the following changes to the measure you created for reference:
For the first slicer, the created First_Result measure obtains the value corresponding to the next week from the date, and the Total measure obtains the cost value corresponding to the week:

Fiset_result = 
VAR min_date = MINX(ALLSELECTED('Date Table'),[Date])

return CALCULATE (
        WEEKNUM ( MAX ( 'Fact'[Date] ) ),
        FILTER ( ALLSELECTED('Fact'), WEEKNUM('Fact'[Date]) = WEEKNUM(min_date)+1
        )
    )

First_total = 
CALCULATE (
    SUM ( 'Fact'[Cost] ),
    FILTER ( ALL ( 'Fact' ), WEEKNUM ( 'Fact'[Date] ) = [Fiset_result] )
)

Filtered cost total results:

If the problem is still not resolved, please provide detailed error information and let me know immediately, looking forward to your reply.

Best Regards,
Henry

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

[nardtmo]

thanks Henry. This helps a lot!!

[PhilipTreacy]

Hi @nardtmo 

Download sample PBIX with data and visuals

This will only work if you have a single year's worth of data.  If you have dates that overlap then you will have weeks that have the same week number.  If this is the case then you need to be able to identify which year/week you want to display data for.

However, I will assume for now that you just have a single year of data as you haven't provided any data to show otherwise.

Do you need 2 slicers for this?  You can do it with just 1.

Your Date Table needs to have the week number in it - you don't mention that it does?  My PBIX does have this.

You can then set up 2 measures like so

Last Week Sales = 
CALCULATE(
    SUM('Table'[Fruits]),
    'Datetable'[WeekNumber] = FILTERS('Datetable'[WeekNumber]) - 1
)

Next Week Sales = 
CALCULATE(
    SUM('Table'[Fruits]),
    'Datetable'[WeekNumber] = FILTERS('Datetable'[WeekNumber]) + 1
)

Regards

Phil 

[nardtmo]

two slicers or two date input needed. Actual data contains start date of the project and end date of the project but these dates dynamically change. Instead of hard coding them in the input file (excel), I want the user to have the ability to set these dates and compare cost prior week (start date week) and week after (end date). 

[PhilipTreacy]

Hi @nardtmo 

So you only have 12 months worth of dates, or do dates span beyond a single year?

Please supply some real data in columns so I know what you are actually working with.

regards

Phil

[nardtmo]

https://www.dropbox.com/sh/3x7ew6h4aahyc12/AACwEnCX8ZXlkhS22IfNajLVa?dl=0

Hi see attached file. This is the one that kinda works but input dates need to be enter in separate excel file. also calculate 7 days before project start and 7 days after project end. not the prevous week or after after.

[lbendlin]

you can't use week numbers like that, not by themselves. Let's assume the user selected week 1 of 2021.  How would you know which week's data to show?

One solution is to add a composite key with the year number and week number, like 202053 and 202101. Then you can write a measure that finds the current yearweek number, then the next largest yearweek number in the past, and then the transactions for that date filter.