group by custom date

My second time writing this.....I’ll try my best to explain

My objective is use slicers to filter out certain visuals.

I am using Percent slicer and want to use a start/end date slider date slicer.

But as of now, I am using a fiscal year drop down slicer with percent slicer.

1 year has a total of 8760 hours. And this is what is displayed here.

The filter is when we compare data% and the slicer’s %.

Slicer % is user defined.

Data% is = (# of rows of hourlydata) / (# of hours in that time period)

This is fine when I apply it to the following visual

I can filter this properly, if I slide it to 70%, then 2 premises remain, if I slide to 72%, then only 1 premise remains.

However when I toss it into the following visual, I was not able to filter it, since the rowcount now is always a count of 1 per row. And 1/8760 = is close to 0, thus filtering out all results.

Which led me to using the following code. This code allowed me to keep consistent count throughout, grouped by premise and date range.

MatrixIntervalcount = SUMMARIZE(loadshape,"rowcount",CALCULATE(countrows(ALLSELECTED(loadshape)),ALLEXCEPT(loadshape,_Dimdate[Fiscal Year],loadshape[Premise])))

As you can see here, the row counts are all constant in every row. 6053 is the rowcount of hourly data during the entire year. 6053/8760 = 69%

Then I used the following code to get the results in the following visual. Now I can filter the visual based on the percent slicer.

MatrixFilteredhourlyData =

var datapercent=SUMMARIZE(loadshape,"rowcount",CALCULATE(countrows(ALLSELECTED(loadshape)),ALLEXCEPT(loadshape,_Dimdate[Fiscal Year],loadshape[Premise])))

VAR value1 = 'PercentSelect'[Percent Value]/100

return

IF((datapercent/[Dateinterval])>=value1,loadshape[Hourlyusagesum],BLANK())

Data Sample

Sample output

This is the desired output. Depending the slicer’s percent. The red arrows premises can be filtered in or out. However due to the code

SUMMARIZE(loadshape,"rowcount",CALCULATE(countrows(ALLSELECTED(loadshape)),ALLEXCEPT(loadshape,_Dimdate[Fiscal Year],loadshape[Premise])))

It only works for fiscal year and the user is limited to that selection.

If it is _dimdate[month], then the limits is selecting only 1 month at a time.

I’d like a customizable range. And since allexcept only accepts columns, would I require to make a dynamic column?