Skip to main content
cancel
Showing results for 
Search instead 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

Reply
mysasai
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: 

mysasai_0-1649676949968.png

 

Sheet1:

mysasai_1-1649677072929.png

 

 

Output:

mysasai_0-1649676152063.png

mysasai_1-1649676172586.png

Tag: @Icey  @amitchandak 

 

Thanks in Advance. 

 

1 ACCEPTED SOLUTION

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. 

View solution in original post

2 REPLIES 2
v-eqin-msft
Community Support
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

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. 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors