Forum Discussion
Subtract previous aggregated week value
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?
3 Replies
- amitchandak
Super User
Anonymous
foe WOW refer
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
week calendar
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.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?- sturlaws
Resident Rockstar
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.
