Forum Discussion
Group by Data Ranges
Hi All,
Need a little assistance with what I presume will require a DAX query.
I currently have a column named 'Days Aging' with the following query: Days Aging = DATEDIFF([Date],TODAY(),DAY)
Simply looking up the date column and assigning a value on how many days it has been since that date.
My source data is always based on the last 30 days,
What I am looking to do is create a simplified slicer to select 'Days Aging' ranges.
For instance, I would like a slicer which has the following options:
Today's data
Last 7 Days
Last 30 Days
When I started writing this post I had in my mind that I would have to query a new column to assign those values in the relevant rows... but looking at that now that method wouldn't be possible as for instance 6 days ago would need to be included within Last 7 Days and Last 30 Days.
Therefore my question is.. is this possible to simplfy a slicer to be able to select the relevant data ranges for;
Today's data
Last 7 Days
Last 30 Days
Thanks all
Elliott First you need a table with the text that you need on the slicer with also a column for sorting and getting the value.
Use "Enter Data" to create a table
Period Sort Today's Data 1 Last 7 Days 2 Last 30 Days 3
The table won't have any relantionship with others ( let's call it "Periods")
Create a new measure that will change based on the slicer selection
VariablePeriod = SWITCH ( MIN ( Periods[Sort] ); 1; CALCULATE ( [yourmeasure]; FILTER ( Table; Table[Days Aging] = 1 ) ); 2; CALCULATE ( [yourmeasure]; FILTER ( Table; Table[Days Aging] > 1 && Table[Days Aging] <= 7 ) ); 3; CALCULATE ( [yourmeasure]; FILTER ( Table; Table[Days Aging] > 30 ) ) )Now add as slicer the Periods field from "Periods Table" and use the [VariablePeriods] for your graphs.
Notice that if there is no selection on the slicer it will calculate the MIN( Periods[Sort]) which is Today's. You can use MAX if you want to show all data.
*I am writing without pbix open, so maybe some mispelling on formula.
* If you go to powerpivotpro.com and search " disconnected slicer" there are many and great posts on the subject
14 Replies
Elliott First you need a table with the text that you need on the slicer with also a column for sorting and getting the value.
Use "Enter Data" to create a table
Period Sort Today's Data 1 Last 7 Days 2 Last 30 Days 3
The table won't have any relantionship with others ( let's call it "Periods")
Create a new measure that will change based on the slicer selection
VariablePeriod = SWITCH ( MIN ( Periods[Sort] ); 1; CALCULATE ( [yourmeasure]; FILTER ( Table; Table[Days Aging] = 1 ) ); 2; CALCULATE ( [yourmeasure]; FILTER ( Table; Table[Days Aging] > 1 && Table[Days Aging] <= 7 ) ); 3; CALCULATE ( [yourmeasure]; FILTER ( Table; Table[Days Aging] > 30 ) ) )Now add as slicer the Periods field from "Periods Table" and use the [VariablePeriods] for your graphs.
Notice that if there is no selection on the slicer it will calculate the MIN( Periods[Sort]) which is Today's. You can use MAX if you want to show all data.
*I am writing without pbix open, so maybe some mispelling on formula.
* If you go to powerpivotpro.com and search " disconnected slicer" there are many and great posts on the subject
What does [yourmeasure] refer to in your comment? I am very new to DAX, so not sure about the syntax.
the name of your measure - replace it with your calculation.
i.e [Sales] or [Total Clients] etc.
Current workaround is to create 3 separate columns for instance...
Last 7 days writes in every relevant row, leaves all others blank
Last 30 day writes in every relevant row, leaves all others blank
Then create seperate slicers for each column / hide titles etc..etc.. don't show blanks.. then allign next to each other..
Would be better in a single slicer but don't think the functionality is there.. please correct me if I'm wrong!
