Supplies are limited. Contact info@espc.tech right away to save your spot before the conference sells out.
Get your discountScore big with last-minute savings on the final tickets to FabCon Vienna. Secure your discount
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
@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))
User | Count |
---|---|
64 | |
59 | |
47 | |
33 | |
32 |
User | Count |
---|---|
84 | |
75 | |
56 | |
50 | |
44 |