Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
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
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
@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
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |