Forum Discussion
Dynamic grouping according to the time range
Hi there,
I am using the Sample data "Financial" from Power BI sample dataset.
I have a dynamic date slicer for the sales date.
What I wanted to do is
1. select the last date from the slicer (say 18-6-2014) and convert it to the end of month (30-6-2014)
2. anything that is 4 months before the last end of month date from slicer will be grouped as one group (28-02-2014)
3. anything that is within the last 4 month of the slicers should group by end of month of each month ("31-03-2014; 30-04-2014; 31-05-2014; 30-06-2014;)
This grouping shall be used on matrix for columns, while I will be using "product" as the rows for this matrix and Sum of sales will be the value.
I have been trying to use calucate with allexcept / all / allselected but somehow the last date from the slicer is always come back incorrectly..
It is either return the last date from all the data (regardless my filter) , or each date on each row of data
Any help or advice is highly welcome!
Hi, Anonymous
You can try the following methods. The end month column was added to the original table.
Measure = Var _Maxselectdate = MAXX(ALLSELECTED('Date'[Date]),'Date'[Date]) Var _lastdate = EOMONTH(_Maxselectdate,-4) VAR _Curmonth = SELECTEDVALUE('financials'[End Month]) VAR _Result = IF( _Curmonth <= _Maxselectdate && _Curmonth >= _lastdate, 1) Return _ResultMeasure Value = Var _Maxselectdate = MAXX(ALLSELECTED('Date'[Date]),'Date'[Date]) VAR _Mindate = EOMONTH(_Maxselectdate,-1) + 1 Var _maxmonthvalue = CALCULATE( SUM(financials[Sales]), 'financials'[Date] >= _Mindate && 'financials'[Date] <= _Maxselectdate) RETURN IF(NOT ISBLANK([Measure]) || ISBLANK(SELECTEDVALUE('financials'[End Month])), IF(ISBLANK(SELECTEDVALUE('financials'[End Month])),_maxmonthvalue,SUM(financials[Sales])))Please see the attached document.
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
It should be sth like this:
When the slicer is set as "before 16-Jun",
the first column (28-Feb) will be the sum of all data that is before 28-Feb.
The second column (31-Mar) will be the sum of all data that is between 1-Mar - 31-Mar
The Third column (30-Apr) will be the sum of all data that is between 1-Apr - 30-Apr
The fourth column (31-May ) will be the sum of all data that is between 1-May - 31-May
The fivth column (31-Jun) will be the sum of all data that is between 1-Jun - 16-Jun
========================When the Slicer updated to "before 15-May",
the first column (31-Jan) will be the sum of all data that is before 31-Jan.
The second column (28-Feb) will be the sum of all data that is between 1-Feb - 28-Feb
The Third column (31-Mar) will be the sum of all data that is between 1-Mar - 31-Mar
The fourth column (30-Apr) will be the sum of all data that is between 1-Apr - 30-Apr
The fivth column (31-May ) will be the sum of all data that is between 1-May - 15-May
Hi, Anonymous
You can try the following methods. The end month column was added to the original table.
Measure = Var _Maxselectdate = MAXX(ALLSELECTED('Date'[Date]),'Date'[Date]) Var _lastdate = EOMONTH(_Maxselectdate,-4) VAR _Curmonth = SELECTEDVALUE('financials'[End Month]) VAR _Result = IF( _Curmonth <= _Maxselectdate && _Curmonth >= _lastdate, 1) Return _ResultMeasure Value = Var _Maxselectdate = MAXX(ALLSELECTED('Date'[Date]),'Date'[Date]) VAR _Mindate = EOMONTH(_Maxselectdate,-1) + 1 Var _maxmonthvalue = CALCULATE( SUM(financials[Sales]), 'financials'[Date] >= _Mindate && 'financials'[Date] <= _Maxselectdate) RETURN IF(NOT ISBLANK([Measure]) || ISBLANK(SELECTEDVALUE('financials'[End Month])), IF(ISBLANK(SELECTEDVALUE('financials'[End Month])),_maxmonthvalue,SUM(financials[Sales])))Please see the attached document.
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.