Date-Range Slicer: Dynamically Change Data Aggregation on Chart based on selected date range

[stalerik]

I have added a slicer with dates as a filter to my report.  I have a table of one-minute data and created a chart based on it.  I would like to change how the data is aggregated on the chart depending on the date range selected in the slicer.

For example, if the date range is less than three days, I'd like to show the one-minute data.  If the date range is between 3 and 30 days, then I'd like to see the one-minute data aggregated to one-hour averages.  If the date range is between 30 and 90 days, then I'd like to show the data on the chart in one-day averages, etc, etc.  Is there a way to change how the chart aggregates the data based on the date range of the slicer?  Thank you. 

[Icey]

Hi @stalerik ,

Try this:

1. Create tables.

Date One-Minute =
ADDCOLUMNS (
    VALUES ( 'Table'[DateTime] ),
    "Date - m", "Date - m",
    "Sum - m", CALCULATE (
        SUM ( 'Table'[Value] ),
        'Table'[DateTime] <= EARLIER ( 'Table'[DateTime] )
    )
)

Date One Hour =
ADDCOLUMNS (
    FILTER (
        VALUES ( 'Table'[DateTime] ),
        MINUTE ( [DateTime] ) - MINUTE ( MINX ( ALLSELECTED ( 'Table' ), [DateTime] ) ) = 0
    ),
    "Date - h", "Date - h",
    "Sum - h", CALCULATE (
        SUM ( 'Table'[Value] ),
        'Table'[DateTime] <= EARLIER ( 'Table'[DateTime] )
    )
)

Date One Day =
ADDCOLUMNS (
    FILTER (
        VALUES ( 'Table'[DateTime] ),
        MOD ( DATEDIFF ( MIN ( 'Table'[DateTime] ), [DateTime], MINUTE ), 60 * 24 ) = 0
    ),
    "Date - d", "Date - d",
    "Sum - d", CALCULATE (
        SUM ( 'Table'[Value] ),
        'Table'[DateTime] <= EARLIER ( 'Table'[DateTime] )
    )
)

2. Union the above three tables.

Dates = UNION('Date One-Minute','Date One Hour','Date One Day')

3. Create a measure.

Measure = 
VAR DateDiff_ =
    DATEDIFF ( MIN ( 'Table'[DateTime] ), MAX ( 'Table'[DateTime] ), DAY )
RETURN
    SWITCH (
        TRUE (),
        DateDiff_ < 3, CALCULATE (
            SUM ( 'Dates'[Sum - m] ),
            FILTER ( Dates, Dates[Date - m] = "Date - m" )
        ),
        DateDiff_ >= 3
            && DateDiff_ < 30, CALCULATE (
            SUM ( 'Dates'[Sum - m] ),
            FILTER ( Dates, Dates[Date - m] = "Date - h" )
        ),
        DateDiff_ >= 30
            && DateDiff_ < 90, CALCULATE (
            SUM ( 'Dates'[Sum - m] ),
            FILTER ( Dates, Dates[Date - m] = "Date - d" )
        )
    )

4. Create visuals.

5. Then you can get this:

Best Regards,

Icey

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

[stalerik]

I don't know if I am smart enough for this.  I have a table now, with a datetime column (minutes) and several value columns.  There are nearly a million rows of minute-data connected to a database with direct query.  I am creating a line chart with a continuous x-axis.  I have a slicer on my report.  I don't understand how to get to my one table and make a line chart that will change the aggregation based on the slicer range (minutes, hours, days, month).  I have tried some of the formulas in these examples but they were not working.

[edhans]

See if this measure helps. You need a data table for this to work.

This first measure just calculates the date range. You could incorporate that into Time Shown measure, but I kept it out for simplicity's sake.

Slicer Days = 
DATEDIFF(
    FIRSTDATE(ALLSELECTED('Date'[Date])),
    LASTDATE(ALLSELECTED('Date'[Date])),
    DAY
)

Then this does your actual calculation. I used "General Format" and put the measure in a card. You can get rid of that but you'll need some mechanism so your audience knows you switch from minutes to hours to days. I let anything above 90 also calculate to days to prevent a BLANK error showing up. You can change the math to show weeks or whatever you want.

Time Shown = 
SWITCH(
    TRUE(),
    [Slicer Days] <=3,
    FORMAT(SUM('Sample Data'[Minute Data]),"General Number") & " Minutes",
    [Slicer Days] > 3 && [Slicer Days] <=30,
    FORMAT(SUM('Sample Data'[Minute Data])/60,"General Number") & "Hours",
    [Slicer Days] > 30 && [Slicer Days] <= 90,
    FORMAT(SUM('Sample Data'[Minute Data])/(60*24), "General Number") & "Days",
    [Slicer Days] > 90,
    FORMAT(SUM('Sample Data'[Minute Data])/(60*24), "General Number") & "Days"
)

 My PBIX file if you want to play around with it.

[amitchandak]

Make sure you have date calendar.

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s.

Refer
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions

The in your formula has a variable

var _diff = datediff(minx(date,date[date]),maxx(date,date[date]),DAY)

Based on this drive rest of the formula.

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601