Forum Discussion

dirkkoch's avatar
dirkkoch
Icon for Helper III rankHelper III
5 years ago

Dynamic Calculation of weekly Total

Dear all, I am currently struggling with a DAX calculation. The objective is to conduct a dynamic calculation related to weekly time intervals.   The starting point is a dynamic value showing tod...
Need Help
v-deddai1-msft's avatar
v-deddai1-msft
Icon for Community Support rankCommunity Support
5 years ago

Hi dirkkoch ,

 

Would you please use the following calculated column based on your sample data:

Total number = IF(ISBLANK('Table'[Inbound])&&ISBLANK('Table'[Outbound]),BLANK(),CALCULATE(SUM('Table'[No. of Parts]), FILTER('Table',INT(LEFT('Table'[Calendar week/year],2))<=WEEKNUM(TODAY()))))+CALCULATE(SUM('Table'[Inbound]), FILTER('Table',INT(LEFT('Table'[Calendar week/year],2))<=INT(LEFT(EARLIER('Table'[Calendar week/year]),2))))-CALCULATE(SUM('Table'[Outbound]), FILTER('Table',INT(LEFT('Table'[Calendar week/year],2))<=INT(LEFT(EARLIER('Table'[Calendar week/year]),2))))

 

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Best Regards,

Dedmon Dai

  • dirkkoch's avatar
    dirkkoch
    Icon for Helper III rankHelper III
    5 years ago

    v-deddai1-msft  Hi Dedmon, thanks for the support. 
    Actually the blank rows/fields are all filled with 0's and the calender week/year is part of a larger date table that has also the full date included (dd.mm.yyyy). Furthermore the first 3 columns are already created measures (let's call them measure1 No. of Parts, measure2 Inbound, measure3 Outbound)

     

    If i put all of this in your formula, it would kind of look like this:

    Total number = IF(measure2 Inbound = "0" && measure3 Outbound="0",
CALCULATE(measure1 No.of Parts, FILTER('Calendar',Date[Date]<=TODAY()))+
CALCULATE(measure2 Inbound, FILTER('Calendar',Date[Date]<=EARLIER('Calendar',Date[Date])))-CALCULATE(measure3 Outbound, FILTER('Calendar',Date[Date]<=EARLIER('Calendar',Date[Date]))))

     First of all, it does not let me apply the Earlier function respectively choose any column after the bracket Earlier(columnName....). How can I get this fixed or is the formula not applicable in my way?