group by custom date

[mikoal]

The following code allows me to perform the action I want, which is counting the roww.

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

However this only works when i select fiscal year. I can change _dimdate to _dimdate[month], day, or year and I can also select the date that corresponds to what I've changed _dimdate to. By matching my date range with the selected column, i am able to group the row counts into the correct count

However I want the date to be flexible and selected by a user using a slicer.

But the problem is Allexcept only accepts columns. Does this mean I need to make a dynamic column that changes with whatever the user selects? How would I achieve this ?

[mikoal]

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?

[Ethan96]

Hi, @mikoal 

You can check this tutorial on displaying dynamic date granularities via slicer selection.

Power BI - Dynamic Date Axis Granularity (Drilldown Alternative) 

Kind Regards,

Ethan

[mikoal]

Thanks Ethan, this video is very interesting.

2 questions.

1)  The video shows day,month, year with specific ranges that falls into these categories. In my case it can show up like 97 days or 1023 days or 367 days. So for 367 days, would dynamic display as 1 year only?

2) The logic allows the user to view the charts at different increments (day, month,year). However since im using 

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

The allexcept requires a column of a date range to be returned, I'm not sure who that would even work. For eg: 367 days would require a column that encompasses 367 days. 5years would require a single period being 5 years long. in order to group it properly. At least this is my thinking.

If there is a different way of approaching this, please let me know.

[mikoal]

i think my response disappeared?

[amitchandak]

@mikoal , First of all summarize will create a calculated column, which will not take any slicer filter

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

The information you have provided is not making the problem clear to me. Can you please explain with an example.

Appreciate your Kudos.

[mikoal]

I'll try my best to explain.

Goal: To filter data based on percent slicer. 

Date slicer for customizable date range.

Slicer = User selects a percentage and date range

We do a comparison with calculated Data Percent VS the slicer %. If the calculated percent is less than slicer %, that customer is filtered out and not displayed.

Calculated Data% = (rowcount of hourly data) divide by (hours of time frame)

If time frame is 1 year, hours of time frame  = 8760

This is what you see here.

The following visual, I am able to filter the "premises" based on the percent (Data % vs slicer %)

The issue comes when the calculated % applied.

when I enter Data% into the following visual, the rowcount would be 1. Then the data % would be 1/8760 = almost 0 and the slicer will filter it out. 

So I used the following 

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

Which allows me to keep the interval count constant . As you can see for premise 6355672_3 it shows 6053 rowcount at every single row. Which allows me to do the correct calculation 6053/8760 = 69%

Then by using the following code, I am able to filter the visual properly.

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())

Unfortunately the method I used is isolated to a timeframe from the Allexcept _dimdate[fiscalyear]. This works but only if I restrict it to fiscal year.

If I used _Dimdate[month], then i will need to restrict users to selecting a month.

This isn't flexible , i want the user to be able to select the date from a slicer. But if I allow that, then the code I use wont work.

If you require more clarity I can try to send a sample file.

Here is a sample data

The desired results is this matrix. The columns filter in and out depending on the Percent slicer.