Dynamic Calculation of weekly Total

[dirkkoch]

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

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!

[v-deddai1-msft]

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]

Hi @v-deddai1-msft , it should be fixed now.

[v-deddai1-msft]

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

[dirkkoch]

Hi @v-deddai1-msft ,
I have fixed the data model (now one to many relationships) but I still do not get the Total calculated precisely as described. Would you mind taking another look at the file: https://1drv.ms/u/s!AhoYRiG8ci7wrxgUbLIhcew8gAXR?e=r4DYyo

Thanks!

[v-deddai1-msft]

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]

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

[dirkkoch]

@v-deddai1-msft  here we go: https://1drv.ms/u/s!AhoYRiG8ci7wrxgUbLIhcew8gAXR?e=r4DYyo

[dirkkoch]

@v-deddai1-msft I would be pleased to do so. How do I have to proceed in order to share with you?

[v-deddai1-msft]

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]

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

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]

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

[dirkkoch]

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. 

[amitchandak]

@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-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8

[dirkkoch]

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