topic Subtract previous aggregated week value in DAX Commands and Tips

Subtract previous aggregated week value

Anonymous — Mon, 27 Jul 2020 09:32:58 GMT

Hi there,

I am trying to find a measure which calculates the aggregated quantity by week and subtracts it from the overall value.

My template is this Excel chart, it basically takes the overall quantity and subtracts the 'Note1 submissions' each given week from the previous week result (note: there are gap weeks with no values)

The column 'Bericht[Geplante Note 1Jahr]' values are weekly Dates in the Format dd.mm.yyyy, therefore there are multiple dates starting at the same Monday of a week.

I tried using this measure but returns blank

Overdue PPAPs = CALCULATE(COUNT(Bericht[Geplante Note 1Jahr]), FILTER(Bericht, Bericht[Note1 Rank] = MAX(Bericht[Note1 Rank]) -1 ))

Is there a way to rebuild the chart above in PowerBI?

Re: Subtract previous aggregated week value

amitchandak — Mon, 27 Jul 2020 09:57:38 GMT

Anonymous

foe WOW refer

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://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3

week calendar

https://community.powerbi.com/t5/Community-Blog/Any-Weekday-Week-Decoding-Date-and-Calendar-2-5-Power-BI-Turning/ba-p/1187482

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184

Appreciate your Kudos.

Re: Subtract previous aggregated week value

Anonymous — Wed, 29 Jul 2020 10:28:46 GMT

Hi amitchandak ,

tried several approaches with your given links and blog posts but the only thing I get is nothing/blank.

Your formulas just simply sum up the values within one week and print them to the start date (WoW). But this is not what I am looking for, because I don't have values on Tuesday, etc, just on Mondays for the entire week.

Therefore, I am rather looking for a formula which takes each week's WTD (in my case Bericht[Geplante Note1 Date], in picture 'Note 1 submissons') and subtracts all previous WTDs including the current WTD from the overall value for each given week, as it can be seen in the screenshot bellow.

I do get the overall value minus the current WTD, but not the sum of all previous WTDs for each week (green bars in chart). Do you know a way to get this?

Re: Subtract previous aggregated week value

sturlaws — Tue, 04 Aug 2020 13:44:56 GMT

Hi Anonymous

Not sure what your data looks like but you can try something like this:

cumulative value = VAR _date = CALCULATE ( MAX ( table[date] ) ) RETURN CALCULATE ( SUM ( table[value] ), FILTER ( ALL ( table ), table[date] <= _date ) )

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.