Forum Discussion

KG1's avatar
KG1
Icon for Resolver I rankResolver I
3 years ago
Solved

Calculated Measure totalling Incorrectly

NBP Tasks Completed This Week = CALCULATE(COUNTA(Test Data'[Order]),DATESBETWEEN(Test Data'[Actual_Finish_date],max(Test Data'[Actual_Finish_date])-weekday(max(Test Data'[Actual_Finish_date]),3),max(Test Data'[Actual_Finish_date])), FILTER(Test Data' , Test Data'[Abort] = "false"))

 

I have the above measure that I can't get the totals in the table to correctly total.

 

I created a new measure incorporating the HASONEFILTER but still get the incorrect total

 

Task Complete for totals = IF(HASONEFILTER('TICOT Benefit Period Data'[Order]),[NBP Tasks Completed This Week],SUMX(VALUES('TICOT Benefit Period Data'[Order]),[NBP Tasks Completed This Week]))
 

The total should be 7305

 

Thank you in advance

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    HI KG1,

    I modify your formula to use year and weeknum to limit calculations range and use weekday as condition to do WTD calculations, you can try to use it if helps.

    NBP Tasks Completed This Week =
VAR currDate =
    MAX ( 'Test Data'[Actual_Finish_date] )
RETURN
    CALCULATE (
        COUNTA ( 'Test Data'[Order] ),
        FILTER (
            ALLSELECTED ( 'Test Data' ),
            'Test Data'[Abort] = "false"
                && YEAR ( 'Test Data'[Actual_Finish_date] ) = YEAR ( currDate )
                && WEEKNUM ( 'Test Data'[Actual_Finish_date] ) = WEEKNUM ( currDate )
                && WEEKDAY ( 'Test Data'[Actual_Finish_date], 3 ) <= WEEKDAY ( currDate, 3 )
        )
    )

    For the total level calculation, you can refer to Greg’s blog to use summarize function to aggregate these calculations on the detail level, then you can use iterator function sumx to summary previous results.

    Measure Totals, The Final Word 

    Regards,

    Xiaoxin Sheng

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    3 years ago

    KG1 , I think this for WTD, refer couple of methods

     

    Have these new columns in Date Table, Week Rank is Important in Date/Week Table

    Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
    OR
    Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format
    WeekDay = weekday([Date],2)

     

    WTD = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank]) && 'Date'[WeekDay]<=max('Date'[WeekDay])))
    LWTD = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1 && 'Date'[WeekDay]<=max('Date'[WeekDay]) ))

     

    Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
    Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

     

    Power BI — Week on Week and WTD
    https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
    https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123
    https://www.youtube.com/watch?v=pnAesWxYgJ8

     

    or use new window function

     

    Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc

     

     

     

    • KG1's avatar
      KG1
      Icon for Resolver I rankResolver I
      3 years ago

      amitchandak thank you very much for your reply - I successfully replicated your measure but it hasn't populated the column total at all

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    HI KG1,

    I modify your formula to use year and weeknum to limit calculations range and use weekday as condition to do WTD calculations, you can try to use it if helps.

    NBP Tasks Completed This Week =
VAR currDate =
    MAX ( 'Test Data'[Actual_Finish_date] )
RETURN
    CALCULATE (
        COUNTA ( 'Test Data'[Order] ),
        FILTER (
            ALLSELECTED ( 'Test Data' ),
            'Test Data'[Abort] = "false"
                && YEAR ( 'Test Data'[Actual_Finish_date] ) = YEAR ( currDate )
                && WEEKNUM ( 'Test Data'[Actual_Finish_date] ) = WEEKNUM ( currDate )
                && WEEKDAY ( 'Test Data'[Actual_Finish_date], 3 ) <= WEEKDAY ( currDate, 3 )
        )
    )

    For the total level calculation, you can refer to Greg’s blog to use summarize function to aggregate these calculations on the detail level, then you can use iterator function sumx to summary previous results.

    Measure Totals, The Final Word 

    Regards,

    Xiaoxin Sheng