Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

 

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.