Dynamic CuttOff date based on week ending date

[Anonymous]

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. 

[Greg_Deckler]

@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.

[Anonymous]

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.. 

[Greg_Deckler]

@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])