Previous week distinct count - Dynamic date range between-slicer

[adityavighne]

Hello All,

I want to use Slicer - Between the date range

I need to calculate data - the table shows Distinct count of the selected date range 25-31 JAN 2019 and the next column shows the previous 7 days data - 18-24 JAN 2019

So I need selected week 25-31 and previous week 18-24 also this date range changes as per selection and calculation for a previous week also.

Kindly help with this.

[v-qiuyu-msft]

Hi @adityavighne ,

You can create measures below: 

SelectedCount = CALCULATE(DISTINCTCOUNT('Table1'[ID]),ALLSELECTED(Table1[Date]))

PrevCount = var StartDate=CALCULATE(MIN('Table1'[Date]),ALLSELECTED(Table1[Date]))

return

CALCULATE(DISTINCTCOUNT(Table1[ID]),FILTER(ALL('Table1'),'Table1'[Date]<StartDate && 'Table1'[Date]>=StartDate-7))

Best Regards,
Qiuyun Yu 

[adityavighne]

Hi,

Thanks for the reply.

still, this measure giving me the same values for all. I don't know why. do you have a solution for this?

[adityavighne]

Your answer is giving data of 25th which is 13. I don't want the previous 7th-day value.

I want to distinct count of all previous 7 days, on basis of date selection.

you can replicate this same data multiple time.

Date	ID	Page
1/1/2019	197190032317476_8447973776680082058	a
1/2/2019	197190032317476_8447973776680082058	b
1/3/2019	197190032317476_8447973776680082058	c
1/4/2019	197190032317476_8447973776680082058	d
1/5/2019	197190032317476_8447973776680082058	a
1/6/2019	197190032317476_8447973776680082058	b
1/7/2019	197190032317476_8447973776680082058	c
1/8/2019	197190032317476_8447973776680082058	d
1/9/2019	197190032317476_8447973776680082058	a
1/10/2019	197190032317476_8447973776680082058	b
1/1/2019	197190032317476_8447973776680082058	c
1/2/2019	197190032317476_8447973776680082058	d
1/3/2019	197190032317476_8447973776680082058	a
1/4/2019	197190032317476_8447973776680082058	b
1/5/2019	197190032317476_8447973776680082058	c
1/6/2019	197190032317476_8447973776680082058	d
1/7/2019	197190032317476_8447973776680082058	a
1/8/2019	197190032317476_8447973776680082058	b
1/9/2019	197190032317476_8447973776680082058	c
1/10/2019	197190032317476_8447973776680082058	d
1/21/2019	197190032317476_8447973776680082058	a
1/22/2019	240206843928821_6961211759885737083	b
1/23/2019	601038483776190_7735836360593054066	c
1/24/2019	601038483776190_7735836360593054066	d
1/25/2019	871268945591513_7132526101485594407	a
1/26/2019	871268945591513_7132526101485594407	b
1/27/2019	871268945591513_7132526101485594407	c
1/28/2019	924367417028370_7492928701214981402	d
1/29/2019	924367417028370_7492928701214981402	a
1/30/2019	976458848054501_9150205732877128846	b
1/31/2019	111595305812628_3947417046463777469	c
2/1/2019	111595305812628_3947417046463777469	d
2/2/2019	111595305812628_3947417046463777469	a
2/3/2019	111595305812628_3947417046463777469	b
2/4/2019	111595305812628_3947417046463777469	c
2/5/2019	111595305812628_3947417046463777469	d
2/6/2019	111595305812628_3947417046463777469	a
2/7/2019	456312410448513_1803518541748429750	b
2/8/2019	561338730605101_2524091600258641980	c
2/9/2019	975908977245798_2575588146540217511	d
2/10/2019	561338730605101_2524091600258641980	a
2/11/2019	813030921949569_6731309020712374740	b
1/31/2019	813030921949569_6731309020712374740	c
2/1/2019	91525604482_6917529027642678403	d
2/2/2019	91525604482_6917529027642678404	a
2/3/2019	210411206300380_3783820045567045205	b
2/4/2019	236366378936516_3199577556528193066	c
2/5/2019	236366378936516_3199577556528193066	d
2/6/2019	28032831195_4611686018428362112	a
2/7/2019	313719516475327_976193923802676546	b
2/8/2019	325905515714656_8464730794280148558	c
2/9/2019	325905515714656_8464730794280148558	d
2/22/2019	325905515714656_8464730794280148558	a
2/23/2019	511737054629859_8887502822576098568	b
2/24/2019	511737054629859_8887502822576098568	c
2/25/2019	511737054629859_8887502822576098568	d
2/26/2019	719420328982500_6255106219615310759	a
2/27/2019	757808328588592_6576602539323115301	b
2/28/2019	96009959044646_8585768053587895667	c
3/1/2019	975908977245798_2575588146540217511	d
3/2/2019	975908977245798_2575588146540217511	a
3/3/2019	113415991117440_4972186124049347874	b
3/4/2019	128634539808414_319656911601974419	c
3/5/2019	128634539808414_319656911601974419	d
3/6/2019	128634539808414_319656911601974419	a
3/7/2019	128634539808414_319656911601974419	b
3/8/2019	128634539808414_319656911601974419	c
3/9/2019	128634539808414_319656911601974419	d
3/10/2019	128634539808414_319656911601974419	a
3/11/2019	128634539808414_319656911601974419	b
3/12/2019	128634539808414_319656911601974419	c
3/13/2019	128634539808414_319656911601974419	d
3/1/2019	128634539808414_319656911601974419	a
3/2/2019	128634539808414_319656911601974419	b
3/3/2019	128634539808414_319656911601974419	c
3/4/2019	196764414215809_8223654677792577539	d
3/5/2019	196764414215809_8223654677792577539	a
3/6/2019	22522112668_6917529027641685833	b
3/7/2019	22522112668_6917529027641685834	c
3/8/2019	272813184619803_8956493669860760235	d
3/9/2019	28964562651_4611686018428362215	a
3/23/2019	28964562651_4611686018428362216	b
3/24/2019	438768319479192_3580915507711873005	c
3/25/2019	500455146427302_680677433280831741	d
3/26/2019	65471778655_4611686018428940550	a
3/27/2019	65471778655_4611686018428940551	b
3/28/2019	671782382512216_3343211043005839716	c
3/29/2019	826862339174853_6257497371105885372	d
3/30/2019	89094099998_4611686018429742527	a
3/31/2019	064878666113544_3397210365041580786	b
4/1/2019	064878666113544_3397210365041580786	c
4/2/2019	433093231140311_2535359322293741002	d
4/3/2019	433093231140311_2535359322293741002	a
4/4/2019	433093231140311_2535359322293741002	b
4/5/2019	433093231140311_2535359322293741002	c
4/6/2019	433093231140311_2535359322293741002	d
4/7/2019	433093231140311_2535359322293741002	a
4/8/2019	433093231140311_2535359322293741002	b
4/9/2019	433093231140311_2535359322293741002	c
4/10/2019	433093231140311_2535359322293741002	d
4/11/2019	51371130587_4611686018428362315	a
4/12/2019	51371130587_4611686018428362316	b
4/13/2019	663033245042090_1392993905040621870	c
4/14/2019	663033245042090_1392993905040621870	d
4/15/2019	663033245042090_1392993905040621870	a
4/16/2019	756118180281553_6003012718445353881	b
4/17/2019	865578752531610_7910682634347150233	c
4/18/2019	865578752531610_7910682634347150233	d
4/19/2019	94320791915_6917529027641425995	a
4/20/2019	94320791915_6917529027641425996	b
4/21/2019	25484284832355_4454362085951793166	c
4/22/2019	25484284832355_4454362085951793166	d
4/23/2019	585684800584734_8529241723173736215	a
4/24/2019	612375618501819_7236046624097960175	b
4/9/2019	612375618501819_7236046624097960175	c
4/10/2019	612375618501819_7236046624097960175	d

Output required - 

[v-qiuyu-msft]

Hi @adityavighne, 

Which filter do you use? I see you use PowerPivot table as a sample, I would suggest you use Power BI desktop to share how you use filters to decide selected week so I can go further to test. 

Best Regards,
Qiuyun Yu 

[adityavighne]

I'm trying to achieve the result like below screenshot in a table, not separate tables.

[adityavighne]

Can someone help with this? 

[v-qiuyu-msft]

Hi @adityavighne,

You can create a measure below: 

PreWeekCount = var StartDate=CALCULATE(MIN('Table1'[Date]),ALLSELECTED(Table1))

var EndDate=CALCULATE(Max('Table1'[Date]),ALLSELECTED(Table1))

return IF(ISINSCOPE(Table1[Page]),CALCULATE(DISTINCTCOUNT(Table1[ID]),FILTER(ALL('Table1'),'Table1'[Date]>=StartDate-7 && Table1[Date]<=EndDate-7&&Table1[Page]=MAX(Table1[Page]))),CALCULATE(DISTINCTCOUNT(Table1[ID]),FILTER(ALL(Table1),'Table1'[Date]>=StartDate-7 && Table1[Date]<=EndDate-7)))

Best Regards,
Qiuyun Yu 

[adityavighne]

Thanks for the reply.

This measure is taking too much time to load the data. Still, I don't see any result.

I think something else needs to try for a change in above DAX