Trying to Add weeks to a date slicer and count

[Anonymous]

I am using a DIRECTQUERY connection to a SQL server table.

I have a date slicer a user will select the previous week M-Sunday.  I am trying to do a count on the week they have sliced(selected in the slicer) and previous 3 weeks, so it is a 4 week total.

I want each week to show different color in a stacked bar chart or clustered bar chart. 

I am looking through other posts and can not quite get the correct calculation or process to get this information.  Please advise what steps or measurements I need to be able to get this information.  I am fairly new to PBI so please bear with me.

Thanks

[v-yuezhe-msft]

@Anonymous,

Do you want to calculate the number of weeks based on slicer selection? If so, create the following measure in your table and check if it returns your expected result.

number of weeks = 
CALCULATE (
DATEDIFF ( MIN(Table[DateKey]), MAX (Table[DateKey] ), WEEK),
ALLSELECTED ( Table[DateKey])
)+3

If the above DAX doesn’t help, please share sample data of your table and post desired result here.

Regards,
Lydia

[Anonymous]

Thank you very much for the information v-yuezhe-msft.  It is not quite looking what I am trying to find out.  I am trying to get a count of a location field in my data.  I am trying to do a rolling week count by area/location.

Current week I am trying to get a count, then the previous week what that count was, 2 weeks ago and 3 weeks ago.  All based off a time slicer the user will select for the current week in question.  I have several other graphs running off this same slicer.  I get the desired affect if I expand the slicer to 4 week span, but my other graphs do not display as needed.

Hope this may help.  Thanks

[v-yuezhe-msft]

@Anonymous,

Please share sample data of your table and post expected result here so that we can provide you appropriate DAX.

Regards,

[Anonymous]

Sorry, I am not able to share my info, but the results are a stacked bar graph with each of the last 4 weeks showing as a different color for the week.

Data is gathered as an entry from a survey.  Multiple rows of answers for a specific survey, but only interested in counting the location answer.

I understand if too difficult to try to put together without data to share.  Seems like it has not been done before in BI or by others.

Thanks

[v-yuezhe-msft]

@Anonymous,

You can share dummy data of the table for us to analyze.

Regards,
Lydia

[Anonymous]

Thanks @v-yuezhe-msft.  Sorry being difficult.  Attaching a sample of my data, other fields but these are what I thought might help.  Thanks 

SubmissionID	Date	Question	Answer
123456	1/1/2017	Location	Here
123456	1/1/2017	Something Else
123456	1/1/2017	Something Else
123456	1/1/2017	Something Else
123456	1/1/2017	Something Else
123456	1/1/2017	Something Else
123456	1/1/2017	Something Else
987654	1/8/2017	Location	There
987654	1/8/2017	Something Else
987654	1/8/2017	Something Else
987654	1/8/2017	Something Else
987654	1/8/2017	Something Else
987654	1/8/2017	Something Else
654987	1/16/2017	Location	Somewhere
654987	1/16/2017	Something Else
654987	1/16/2017	Something Else
654987	1/16/2017	Something Else
654987	1/16/2017	Something Else
654987	1/16/2017	Something Else
654987	1/16/2017	Something Else
654987	1/16/2017	Something Else
321789	1/24/2017	Location	Somewhere Else
321789	1/24/2017	Something Else
321789	1/24/2017	Something Else
321789	1/24/2017	Something Else
321789	1/24/2017	Something Else
321789	1/24/2017	Something Else
321789	1/24/2017	Something Else
321789	1/24/2017	Something Else
321789	1/24/2017	Something Else

[v-yuezhe-msft]

@Anonymous,

Thanks for your sharing. Could you please post the expected result based on above sample data?

Regards,

[Anonymous]

Sorry looking for a bar stack by area and it shows the last 4 weeks.  Meaning each week is stacked for that area.  Each stack is a different location.

[v-yuezhe-msft]

@Anonymous,

Do you want to calculate the cumulative count of Answer for different questions? If so, please create the following measures in your table.

Measure = COUNT(Table1[Answer])

rollingcount = CALCULATE([Measure],FILTER(ALLEXCEPT(Table1,Table1[Question]),Table1[Date]<=MAX(Table1[Date])))



Regards,
Lydia

[Anonymous]

Sorry thought we were getting closer but I was able to get it pull to correctly show the numbers I was grabbing before and it is not showing the previous 3 weeks numbers also.

Example; I have the slicer pulling for a week, 8/14/17 to 8/20/17, and I want to display these count results but also another color showing the previous week count, a different color of another count of the 2 weeks prior than the slicer nad a last of 3 weeks prior than slicer.

I will have other charts pulling from this same slicer so I can only pull the one week I needed for them and this chart needs to add 3 plus weeks prior.

I am not certain this can be done or if anyone else have ever done.  Looking as though it is not an option.  Please advise if it is not an option and I will need to try to do a work around.

Thanks

[v-yuezhe-msft]

@Anonymous,

You would need to create three measures that represent last week count, last 2 week count and last 3 week count, then drag the three measure to Value section of the column chart, however, in this case, you would need to remove field from Legend section of the column chart. 

And you can refer to the following similar threads to calculate count number of previous weeks .

http://community.powerbi.com/t5/Desktop/previous-week-sales/td-p/189663
https://community.powerbi.com/t5/Desktop/Getting-Value-Sum-from-previous-week/td-p/107816

Regards,
Lydia

[Anonymous]

ANother promising direction, but I want the user to be able to use a date slicer.  I was pulling the data and getting previous weeks, kept subtracting days, 7 14 21 for weeks.  When I added the date slicer in and just showed the current week I had in the slicer and no information in the previous weeks. 

I am thinking there is a way to do a day subtraction from the submission dates from the slicer.  I am normally running this on a weekly basis  on a scale M-Sunday.  is there a way to do subtractions on dates by day from the slicer submission date selection?  Thanks 

[v-yuezhe-msft]

@Anonymous,

You should add a calendar table in your data model, create relationship between the calendar table and your original table. Then create measures by passing the date column of the calendar table.

As long as you don't use ALL() function in your measure, the subtractions will be performed on dates by day from slicer selection.

Regards,
Lydia

[Anonymous]

I do not have backend access to my SQL pull to create a table for my connection.  Sorry

[v-yuezhe-msft]

@Anonymous,

You can contact the SQL administrator to create the table for you.

Regards,
Lydia

[Anonymous]

I will take a look at these links thanks.  Hope they are ok with a DirectQuery connection and a slicer.

WIll post if they are working or a solution.  Thanks