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 today's status of Total number (this has been calculated by a measure "start1", in the example below displayed with value 31). This starting value is the input for a further dynamic calculation per calendar week (see logic chart below). Calender week/yearDelta Inbound_OutboundTotal number 031 (dynamic starting value calculated per Measure)08/21 -131+(-1) = 3009/21 230 +2 = 3210/21 332 + 3 = 3511/21 135 + 1 = 3612/21 -136 + (-1) = 3513/21 035 + 0 = 35 The calculated Total number within a week (starting with the current week) is always the new input for the upcoming week’s calculation. With the formula/measure I am using at the moment the input for the calculation is always the starting value also for each following week (-> 31) which results in the incorrect Total number per week.I would be very thankful if you could support in fixing the DAX calculation/measure!

dirkkoch , Part of this seems like WTD, refer my blog for that Power BI — Week on Week and WTD https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123https://www.youtube.com/watch?v=pnAesWxYgJ8

amitchandak , thank you for the reply and support.I looked up your very helpful blog and added all missing columns to my date table as shown in your overview. But still I am missing the right approach to create the required DAX measure in detail to get the desired result as shown in the first message. The first Total number is calculated per measure in one separate table related with the date table No of engines = var count=CALCULATE(COUNTROWS(T050_DATA_BATCH),T050_DATA_BATCH[Abstellort Motor]="Mannheim - SGZ", T050_DATA_BATCH[Inbound Mannheim Plandatum].[Date]<=TODAY())returnif(isblank(count),0 ,count) This measure results in 31 which now should be used for further calculation logic per week as mentioned in the first message. Do you mind to give the exact DAX measure to apply for this calculation. Thank you again for the support! Highly appreciated.

Maybe I have to specify my problem formulation in order to solve my issue.The following table displays the problem more precise. I want to take the sum (No. of Parts, yellow line) until the current week/date (here CW08/21 or Today) and afterwards take this measure as input for a further calculation starting from this week/Today on (orange column).It should be dynamic, meaning the input sum value (yellow line) as well as the Total number (orange column) will change over the course of time each week since I am always referring to the current week/date (next week 09/21...). How can I do that and what exact DAX calculation can solve this issue.

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

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?

Dynamic Calculation of weekly Total | Microsoft Fabric Community

15 Replies

amitchandak
Super User
5 years ago
dirkkoch , Part of this seems like WTD, refer my blog for that

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
- dirkkoch
  Helper III
  5 years ago
  amitchandak , thank you for the reply and support.
  I looked up your very helpful blog and added all missing columns to my date table as shown in your overview.
  
  But still I am missing the right approach to create the required DAX measure in detail to get the desired result as shown in the first message.
  
  The first Total number is calculated per measure in one separate table related with the date table
  No of engines =
  var count=CALCULATE(COUNTROWS(T050_DATA_BATCH),T050_DATA_BATCH[Abstellort Motor]="Mannheim - SGZ", T050_DATA_BATCH[Inbound Mannheim Plandatum].[Date]<=TODAY())
  return
  if(isblank(count),0 ,count)
  
  This measure results in 31 which now should be used for further calculation logic per week as mentioned in the first message. Do you mind to give the exact DAX measure to apply for this calculation.
  Thank you again for the support! Highly appreciated.
dirkkoch
Helper III
5 years ago
Maybe I have to specify my problem formulation in order to solve my issue.
The following table displays the problem more precise.
I want to take the sum (No. of Parts, yellow line) until the current week/date (here CW08/21 or Today) and afterwards take this measure as input for a further calculation starting from this week/Today on (orange column).
It should be dynamic, meaning the input sum value (yellow line) as well as the Total number (orange column) will change over the course of time each week since I am always referring to the current week/date (next week 09/21...).
How can I do that and what exact DAX calculation can solve this issue.
v-deddai1-msft
Community 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
  Helper 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?
v-deddai1-msft
Community Support
5 years ago
Hi dirkkoch ,

If you are using measure, please try to use the following measure:

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]<=MAX(Date[Date])))-CALCULATE(measure3 Outbound, FILTER('Calendar',Date[Date]<=MAX(Date[Date])))

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
  Helper III
  5 years ago
  v-deddai1-msft
  I think we are getting closer to a solution. If I use your formula with my measures, then I only receive the difference/delta between inbound & outbound, meaning the sum of 31 is not set as a starting value for further calculation (see Screenshot 1)
  
  Total number = If([Anzahl Motoren Inbound]=VALUE("0") && [Anzahl Motoren Outbound]=Value("0"), Blank(),
  Calculate([Anzahl Motoren SGZ neu], FILTER(Kalender, Kalender[Datum]<=Today()))+
  Calculate([Anzahl Motoren Inbound], Filter(Kalender, Kalender[Datum]<=Max(Kalender[Datum])))-
  Calculate([Anzahl Motoren Outbound], Filter(Kalender, Kalender[Datum]<=Max(Kalender[Datum]))))
  
  The closest I get is when I use the following DAX calculation, unfortunately in this case the sum of 31 is taken as input for each future week and not only for the current week:
  
  Total number = If([Anzahl Motoren Inbound]=VALUE("0") && [Anzahl Motoren Outbound]=Value("0"), Blank(),
  Calculate([Anzahl Motoren SGZ], ALLEXCEPT(Kalender, Kalender[Datum]))+
  Calculate([Anzahl Motoren Inbound], Filter(Kalender, Kalender[Datum]<=Max(Kalender[Datum])))-
  Calculate([Anzahl Motoren Outbound], Filter(Kalender, Kalender[Datum]<=Max(Kalender[Datum]))))
v-deddai1-msft
Community Support
5 years ago
Hi dirkkoch ,

There is some row context prevents measure from being evaluated correctly. It's better you can show us sample pbix by onedrive for business. Then I can help you more accurately.

Best Regards,
Dedmon Dai
- dirkkoch
  Helper III
  5 years ago
  v-deddai1-msft I would be pleased to do so. How do I have to proceed in order to share with you?
- dirkkoch
  Helper III
  5 years ago
  v-deddai1-msft here we go: https://1drv.ms/u/s!AhoYRiG8ci7wrxgUbLIhcew8gAXR?e=r4DYyo
- dirkkoch
  Helper III
  5 years ago
  v-deddai1-msft
  Dear Dedmon,
  I hope the uploaded .pbix file gave you more insight on where the incorrect calculation came from?! Do you have any new hint yet? Thanks again for the support!
v-deddai1-msft
Community Support
5 years ago
Hi dirkkoch ,

Sorry for late response. There's something wrong with content in your pbix file. Would you please check for it?

Best Regards,
Dedmon Dai
- dirkkoch
  Helper III
  5 years ago
  Hi v-deddai1-msft , it should be fixed now.
  - v-deddai1-msft
    Community Support
    5 years ago
    Hi dirkkoch ,
    
    Your sample data model is in a mess. It contains many to many relationship which prevents the formula from calculated precisely.
    
    Best Regards,
    Dedmon Dai

Forum Discussion

Dynamic Calculation of weekly Total

15 Replies

Related content

Dynamic Rank Calculation

Weekly Average

Weekly Check Calculation

Dynamic Variable in Calculated Columns

Dynamic Calculation Different Period

Calender week/year	Delta Inbound_Outbound	Total number
	0	31 (dynamic starting value calculated per Measure)
08/21	-1	31+(-1) = 30
09/21	2	30 +2 = 32
10/21	3	32 + 3 = 35
11/21	1	35 + 1 = 36
12/21	-1	36 + (-1) = 35
13/21	0	35 + 0 = 35