Forum Discussion
Dynamic CuttOff date based on week ending date
hey, ill try to explain my problem the best i can.
i have a client that this is his data calculation:
what it means:
they generate this report every monday, and cutoff date is alway last friday date (meaning if they generated this report on 19/09, last friday date is 16/09, and so on)
9-22 Week 3 meaning - report generated in monday (19/09) and the cutoff date is 16/09 (last friday).
9-22 week 2 meaning - report generate in monday (12/09) and the cutoff date is 09/09.
and so on..
since they generate this in execl and make maunal calulation, the demand is to every week generated i need to calculate the current cutoff.
meaning - report generated on monday (19/09), the cutoff in powerbi will be (16/09) - but i need this value only for 9-22 week 3.
in order to calculate as they want, for 9-22 week 2, the cutoff should be 09/09.
how can i make a dynamic measure / (or whatever it takes) to achive this calculation?
in order to undersatnd more, this is how i suppose to visualize it:
so of course i can only get the last value becuase i have a column with cutoff date (not dynamic).
can you please help me?
thanks a lot.
3 Replies
Anonymous Not positive I completely understand. So in your column for 09/09/2022 what data in the report should be included? Between that date and the next Monday or between 02/09/2022 or ? Sorry, not entirely following this. Better sample data as text might help as well as expected results.
hey greg,
its kinda complicated.
yes, if are on 09/09 data should be for this week, like the cutoff date is 02/09, for 02/09 cutoff should be a week before - 26/08
and i need to make dynamic calculation based on this on the fly.
all i can think off is nested if's.. but i bet theres another and better way.
so lets say i load the data today, ok?
today is (9-22 week 3, for this purpose, even tho im not showing this to the end user), so:
cutoff date for this week should be in calculation 16/09
cutoff date for last week (9-22 week 2) should be 09/09
cutoff date for 2 weeks before should be 02/09 (9- 22 week 1)
and so on..
POV running to close won conversion = -------------===============================Prep Var's For Calculations==================================-------------------- VAR Cutoff = /* This is the cutoff var that all the clacultion will relay on. */ IF(MAX(DimDate[Fixed WeekEnding]) = MAX(DimDate[Cutoff Date]), DATE(2022,09,16), DATE(2022,09,09)) VAR MINDateKpi7 = CALCULATE(MIN(OpportunitiesFact[3.5 POV Running Date]), AND(OpportunitiesFact[3.5 POV Running Date] > (Cutoff - 180) , OpportunitiesFact[3.5 POV Running Date] < Cutoff)) VAR MAXDateKpi7 = CALCULATE(MAX(OpportunitiesFact[3.5 POV Running Date]), AND(OpportunitiesFact[3.5 POV Running Date] > (Cutoff - 180) , OpportunitiesFact[3.5 POV Running Date] < Cutoff)) -----------------------------==========Actual Measure Calculation----------------================================= VAR demonimator = CALCULATE ( COUNT ( OpportunitiesFact[InitialMeetingDate] ), OpportunitiesFact[Stage]IN {"2.0 - BANT Qualified","3.0 - POV Planning","3.25 - POV Confirmed","3.5 - POV Running","4.0 - POV Completed","5.0 - Technical Win", "6.0 - Proposal Sent","8.0 - Procurement","9.0 - Closed Won","Closed Lost - Competitor","Closed Lost - No Decision"},OpportunitiesFact[KPI 7] = "KPI 7", ALL ( DimDate[Fixed WeekEnding] ) ) VAR Numinator = IF(MAX(DimDate[Fixed WeekEnding]) = Cutoff, CALCULATE ( COUNT ( OpportunitiesFact[InitialMeetingDate] ), OpportunitiesFact[Stage] = "9.0 - Closed Won", OpportunitiesFact[Cutoff date] = Cutoff, OpportunitiesFact[3.5 POV Running Date] >= MINDateKpi7 && OpportunitiesFact[3.5 POV Running Date] <= MAXDateKpi7, ALL ( DimDate[Fixed WeekEnding] )), IF( MAX(DimDate[Fixed WeekEnding]) - 7 = DATE(2022,09,09) , CALCULATE ( COUNT ( OpportunitiesFact[InitialMeetingDate] ), OpportunitiesFact[Stage] = "9.0 - Closed Won", OpportunitiesFact[Cutoff date] = DATE(2022,09,09), OpportunitiesFact[3.5 POV Running Date] >= MINDateKpi7 && OpportunitiesFact[3.5 POV Running Date] <= MAXDateKpi7, ALL ( DimDate[Fixed WeekEnding] )) )) RETURN --if( MAX(DimDate[Fixed WeekEnding]) = MAX(DimDate[Cutoff Date]), CALCULATE(DIVIDE ( Numinator, demonimator )), ) --IF(MAX(DimDate[Cutoff Date]) = Cutoff , 1, 0) DIVIDE ( Numinator, demonimator ) --SELECTEDVALUE(OpportunitiesFact[Cutoff Date]) -- Fri, 16 Sep 2022 00:00:00 -------=========================================UNUSED VARS============================================--------- /* VAR DATES = CALCULATE(DATESBETWEEN(DimDate[Date], MIN(DimDate[StartOfWeek]), MAX(DimDate[Fixed WeekEnding])), DimDate[StartOfWeek] >= Cutoff - 7 && DimDate[Fixed WeekEnding] <= Cutoff) VAR KPI7 = IF(FIRSTDATE(OpportunitiesFact[3.5 POV Running Date]) = BLANK(),BLANK(), IF(AND(FIRSTDATE(OpportunitiesFact[3.5 POV Running Date]) > (Cutoff - 180) , FIRSTDATE(OpportunitiesFact[3.5 POV Running Date]) < Cutoff),"KPI 7","")) VAR CUTOFFOLD = --IF(MAX(DimDate[Last Fridy Day]) = MAX(DimDate[Last Fridy Day]) , --CALCULATE(MAX(DimDate[Date]) , DimDate[Last Fridy Day] = MAX(DimDate[Last Fridy Day]), ALL(OpportunitiesFact))) */this is what i tried to do. but without success..
Anonymous So I've done some things like this. Generally it goes something like:
Measure = //if the date in your matrix is always for a Friday. VAR __EndDate = MAX('Date'[Date]) VAR __StartDate = __EndDate - 7 VAR __Table = FILTER(ALL('Table'),[Date]>=__StartDate && [Date]<=EndDate) RETURN SUMX(__Table,[Value]) or Measure = // if you don't know what day of the week you are going to have VAR __EndDate = MAX('Date'[Date]) VAR __StartDate = MINX( FILTER( ADDCOLUMNS( CALENDAR(__EndDate - 10, __EndDate), "__WeekDay", WEEKDAY([Date]) ), [__WeekDay] = 6 ), [Date] ) VAR __Table = FILTER(ALL('Table'),[Date]>=__StartDate && [Date]<=EndDate) RETURN SUMX(__Table,[Value])
