Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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...
Need Help
Greg_Deckler's avatar
Greg_Deckler
Community Champion
3 years ago

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's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
      Greg_Deckler
      Community Champion
      3 years ago

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