Displaying Data for every N Months (Every 3 Months , Every 12 Months)

[Udit_C]

The Use Case :
Once you have selected the Start and End Date in their respective slicers

We get 4 options to choose from in the Frequency slicer :

1. Daily
- Select Start and End Date in the slicer
- Display the data based on the date Selection

2. Monthly
- Select Start and End Date in the slicer
- Display the data for All Month End Dates between the Range


3. 3 Months
- Select Start and End Date in the slicer
- Display the data for All Month End Dates between the Range
BUT now the difference between the month end dates should be 3 Months and not 1 month.


4. 12 Months
- Select Start and End Date in the slicer
- Display the data for All Month End Dates between the Range
BUT now the difference between the month end dates Should be 12 Months.

For futher reference please find the attached screenshots of different output scenarios

[v-yangliu-msft]

Hi  @Udit_C ,

I created some data:

Here are the steps you can follow：

1. Use Enter data to create a table.

2. Create measure.

Daily =
var _selectmindate=MInX(ALLSELECTED('Table'),'Table'[Date])
var _selectmaxdate=MAXX(ALLSELECTED('Table'),'Table'[Date])
return
IF(
    MAX('Table'[Date])>=_selectmindate&&MAX('Table'[Date])<=_selectmaxdate,1,0)

Monthly =
var _selectmindate=MInX(ALLSELECTED('Table'),'Table'[Date])
var _selectmaxdate=MAXX(ALLSELECTED('Table'),'Table'[Date])
return
IF(
    MAX('Table'[Date])>=DATE(YEAR(_selectmindate),MONTH(_selectmindate),1)&&MAX('Table'[Date])<=EOMONTH(_selectmaxdate,0),1,0)

3 Months =
var _selectmindate=MInX(ALLSELECTED('Table'),'Table'[Date])
var _selectmaxdate=MAXX(ALLSELECTED('Table'),'Table'[Date])
return
IF(MOD(
    MONTH(MAX('Table'[Date])),3)=0&&MAX('Table'[Date])=EOMONTH(MAX('Table'[Date]),0),1,0)

12 Months =
var _selectmindate=MInX(ALLSELECTED('Table'),'Table'[Date])
var _selectmaxdate=MAXX(ALLSELECTED('Table'),'Table'[Date])
return
IF(
DAY(MAX('Table'[Date]))= DAY(EOMONTH(_selectmaxdate,0))&&
MONTH(MAX('Table'[Date]))= MONTH( EOMONTH(_selectmaxdate,0))
,1,0)

Flag =
var _selectfre=SELECTEDVALUE('Slice'[Frequency])
return
SWITCH(
    TRUE(),
    _selectfre="Daily",[Daily],
    _selectfre="Monthly",[Monthly], 
    _selectfre="Every Months",[3 Months],
    _selectfre="Every 12 Months",[12 Months]
    )

3. Place [Flag]in Filters, set is=1, apply filter.

4. Result:

Please click here for the pbix file

Best Regards,

Liu Yang

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

[Udit_C]

Hi Liu Yang,

Thanks for taking the time and sharing your solution.
I did go through the pbix file you shared.
The filter shows only quarter ends (Eg:  March, June, September, December);
whereas my requirement is to show month end data with 3 months gap from max selected date through entire period selected. (if period selected is 30 Sep 2019 to 28 Feb 2022 then the table should show : Feb , Nov, Aug, May through out the selected period)

Regards,
Udit Chaturvedi

[amitchandak]

@Udit_C , In case of month and year you can use month and year end date as filter

 calculate([net], filter('Date',Date[Date] = eomonth( Date[Date],0) )

calculate([net], filter('Date',Date[Date] = eomonth( Date[Date],-1(12-1* month(max(Date[Date])) ))))

for other you can use mod

like

 calculate([net], filter('Date', minx(allselected(Date, Date[Date]), Date[Date],day)+1 =28  ) )

you can merge all there into a switch