Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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 over...
COUNT
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      sturlaws
      Icon for Resident Rockstar rankResident Rockstar
      6 years ago

      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.