cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Udit_C
Frequent Visitor

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

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
Daily_Frequency_1.png

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

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.
Quarterly_Output_1.png

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.
Yearly_output_1.png

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


3 REPLIES 3
v-yangliu-msft
Community Support
Community Support

Hi  @Udit_C ,

I created some data:

vyangliumsft_0-1649381087360.png

Here are the steps you can follow:

1. Use Enter data to create a table.

vyangliumsft_1-1649381087363.png

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.

vyangliumsft_2-1649381087364.png

4. Result:

vyangliumsft_3-1649381087365.png

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

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
Super User
Super User

@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

 

 

 



Power BI Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s || 50+Power Query List Functions : Learn Power Query List
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!
Want To Learn Power BI | Beginners !! Advance Concepts !! Power BI For Tableau User !!

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show episode 9

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 25

Ted's Dev Camp - August 25, 2022

Watch Session 25 of Ted's Dev Camp.

Top Solution Authors