cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## RANKX with daily data to filter Month end data

Hey All,

I previously posted this requirement with monthly data and could find solution here: https://community.powerbi.com/t5/Desktop/Filter-a-table-visual-with-three-or-six-months-gap-based-on...

I used this logic to replicate similar requirement with daily data.

Somehow, quarterly and annually are showing wrong data for some period selection.

Logic works correct when max date selected in Slicer is 11/30/2021. When max date on slicer is moved to 9/30/2021, table shows more data than required. (both screenshots below)

Can you please let me know what's wrong in the RANKX calc that I used.

EOM = IF ('Sheet1'[Date] = EOMonth('Sheet1'[Date],0),1,0)

-----------------------------------------------------------

Filter Measure = IF (SELECTEDVALUE ( 'Monthly Interval'[Filter] )= "Daily" , 1,
IF (AND (SELECTEDVALUE ('Sheet1'[EOM]) = 1 , SELECTEDVALUE ( 'Monthly Interval'[Filter] )= "Monthly"),1,
VAR SelectedMonthInterval_ =
SELECTEDVALUE ( 'Monthly Interval'[Month Interval] )
VAR Rank_ =
CALCULATE( RANKX (
ALLSELECTED ( 'Sheet1' ),
CALCULATE ( MAX ( 'Sheet1'[Date])),
,
DESC,
DENSE
), 'Sheet1'[EOM] = 1)
VAR Mod_ =
MOD ( Rank_, SelectedMonthInterval_ )
RETURN
IF( Mod_ = 1, 1 )))

----------------------------------------------------------------

Monthly Interval:

Sheet1:

Output:

1 ACCEPTED SOLUTION
Frequent Visitor

Eyelyn, Thanks for responding. I figured it out some how.

I created multiple measure for Daily, Monthly, Quarterly, Annually and binded all of them into a switch  function and it worked for me.

Below is how I acheived it:

Calculated Column :

EOM = IF ('Sheet1'[Date] = EOMonth('Sheet1'[Date],0),1,0)

Calculated Measures:
1.Daily = IF (SELECTEDVALUE ( 'Monthly Interval'[Filter] )= "Daily" , 1,0)

2.Monthly = IF (AND (SELECTEDVALUE ('Sheet1'[EOM]) = 1 , SELECTEDVALUE ( 'Monthly Interval'[Filter] )= "Monthly"),1,0)

3.Quarterly = IF (AND (SELECTEDVALUE ('Sheet1'[EOM]) = 1 , SELECTEDVALUE ( 'Monthly Interval'[Filter] )= "Quarterly"),
VAR Rank_ =
CALCULATE(
RANKX (
ALLSELECTED ( 'Sheet1' ),
CALCULATE ( MAX ( 'Sheet1'[Date])),
,
DESC,
DENSE
), 'Sheet1'[EOM] = 1 )
VAR Mod_ =
MOD ( Rank_, 3 )
RETURN
IF( Mod_ = 1, 1 ),0)

4.Annually = IF (AND (SELECTEDVALUE ('Sheet1'[EOM]) = 1 , SELECTEDVALUE ( 'Monthly Interval'[Filter] )= "Annually"),
VAR Rank_ =
CALCULATE(
RANKX (
ALLSELECTED ( 'Sheet1' ),
CALCULATE ( MAX ( 'Sheet1'[Date])),
,
DESC,
DENSE
), 'Sheet1'[EOM] = 1)
VAR Mod_ =
MOD ( Rank_, 12 )
RETURN
IF( Mod_ = 1, 1 ),0)

------------

Binded above measures in Switch function:
5.Flag =
var _selectfre=SELECTEDVALUE('Monthly Interval'[Filter])
return
SWITCH(
TRUE(),
_selectfre="Daily",[1.Daily],
_selectfre="Monthly",[2.Monthly],
_selectfre="Quarterly",[3.Quarterly],
_selectfre="Annually",[4.Annually]
)

and used this measure as filter 'is = 1' in visual filter.
2 REPLIES 2
Community Support

Hi @mysasai ,

Can you please share more details to help us clarify your scenario? Such as your data tables, your expected output or share me with your pbix file after removing sensitive data.

Best Regards,
Eyelyn Qin

Frequent Visitor

Eyelyn, Thanks for responding. I figured it out some how.

I created multiple measure for Daily, Monthly, Quarterly, Annually and binded all of them into a switch  function and it worked for me.

Below is how I acheived it:

Calculated Column :

EOM = IF ('Sheet1'[Date] = EOMonth('Sheet1'[Date],0),1,0)

Calculated Measures:
1.Daily = IF (SELECTEDVALUE ( 'Monthly Interval'[Filter] )= "Daily" , 1,0)

2.Monthly = IF (AND (SELECTEDVALUE ('Sheet1'[EOM]) = 1 , SELECTEDVALUE ( 'Monthly Interval'[Filter] )= "Monthly"),1,0)

3.Quarterly = IF (AND (SELECTEDVALUE ('Sheet1'[EOM]) = 1 , SELECTEDVALUE ( 'Monthly Interval'[Filter] )= "Quarterly"),
VAR Rank_ =
CALCULATE(
RANKX (
ALLSELECTED ( 'Sheet1' ),
CALCULATE ( MAX ( 'Sheet1'[Date])),
,
DESC,
DENSE
), 'Sheet1'[EOM] = 1 )
VAR Mod_ =
MOD ( Rank_, 3 )
RETURN
IF( Mod_ = 1, 1 ),0)

4.Annually = IF (AND (SELECTEDVALUE ('Sheet1'[EOM]) = 1 , SELECTEDVALUE ( 'Monthly Interval'[Filter] )= "Annually"),
VAR Rank_ =
CALCULATE(
RANKX (
ALLSELECTED ( 'Sheet1' ),
CALCULATE ( MAX ( 'Sheet1'[Date])),
,
DESC,
DENSE
), 'Sheet1'[EOM] = 1)
VAR Mod_ =
MOD ( Rank_, 12 )
RETURN
IF( Mod_ = 1, 1 ),0)

------------

Binded above measures in Switch function:
5.Flag =
var _selectfre=SELECTEDVALUE('Monthly Interval'[Filter])
return
SWITCH(
TRUE(),
_selectfre="Daily",[1.Daily],
_selectfre="Monthly",[2.Monthly],
_selectfre="Quarterly",[3.Quarterly],
_selectfre="Annually",[4.Annually]
)

and used this measure as filter 'is = 1' in visual filter.