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

Score big with last-minute savings on the final tickets to FabCon Vienna. Secure your discount

Reply
UmangPaliwal
Frequent Visitor

Enable maximum date selection for Weekly report so that it can show Latest Week, WoW, MoM & YoY

Hi All,

I've created a report for which the sample i'm sharing here. This is a weekly report for which the data comes in every friday.
Goal is to show data over 4 parameters which are as follows:-
Latest Week:- Current Friday Sum
WoW:- Latest Week - Previous Week (i.e. Last Friday)
MoM:- Latest Week - Last Friday of Previous Month
YoY:- Latest Week - Last Friday same month of Last Year.

I'm able to make these measures correctly. However for them to display correct data i currently need to pass the maximum published date as filter. This is preventing the report from auto refreshing every friday as i have to manually select Maximum date for the report to work.

I tried the solution with a calculated column that returns "Latest Date" for maximum date in dataset. That, however,  is showing incorrect values.

Please help me solve this so that i can have maximum date selected by default.
Everything is included in attached PBIX. 
https://drive.google.com/file/d/1AXzNZnWhPTulr104sZiL4OLMwUAr66Jt/view?usp=sharing

@amitchandak @parry2k  @Greg_Deckler @Jihwan_Kim 

1 REPLY 1
amitchandak
Super User
Super User

@UmangPaliwal , seem like you have date, you can join that with date table and try like

 

YTD =
var _max = if(isfiltered('Date'),MAX( 'Date'[Date]) , MAXX(allselecetd('Date'), 'Date'[Date]))
var _min = eomonth(_max,-1*MONTH(_max))+1
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

LYTD =
var _max1 = if(isfiltered('Date'),MAX( 'Date'[Date]) , MAXX(allselecetd('Date'), 'Date'[Date]))
var _max = Date(Year(_max1)-1, Month(_max1), Day(_max1))
var _min = eomonth(_max,-1*MONTH(_max))+1
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

 

MTD =
var _max = min(MAX( 'Date'[Date]) , MAXX(allselected('Table'), 'Table'[Sales Date]))
var _min = eomonth(_max,-1)+1 ,
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

 

 

This Week =

var _max = min(MAX( 'Date'[Date]) , MAXX(allselected('Table'), 'Table'[Sales Date]))

var _maxR = maxx(all('Date'), 'Date'[Date] =_max  ), Date[Week Rank])

return

CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=_maxR )))
Last Week =

var _max = min(MAX( 'Date'[Date]) , MAXX(allselected('Table'), 'Table'[Sales Date]))

var _maxR = maxx(all('Date'), 'Date'[Date] =_max  ), Date[Week Rank])

return

CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=_maxR -1))

 

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

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