Dynamic grouping according to the time range

[BT_2343414234]

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!

[BT_2343414234]

@Anonymous thanks a lot, it works perfectly! 

[BT_2343414234]

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

[Anonymous · Accepted solution]

Hi, @BT_2343414234 

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
_Result

Measure 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.

[Anonymous]

Hi, @BT_2343414234 

What kind of output do you expect? Please picture or give an example.

Best Regards