Turning TYD data into monthly data

[Enriquemmesteo]

Hello everyone,

I am having some trouble with a new request.

Usually we have the data (lets say sales) in the following manner:

for each period we have the sales, so we load period and sales and we can represent monthly sales as sum(sales) for periods or the cumulative sales with the RangeSum(Above(sum(sales),0,RowNo(TOTAL))).

Now for the budget, the data we have is the cumulative budget. In other words we have the second of the previous measures directly. To get the monthy values for the budget we would need to substract, for each period the budget of the previous period.

How could we achieve this? I tried creating a variable v_prevmonth =date(addmonths(DATE(Period_Date),-1),'YYYY.MM') which gives me the value of the previous month, and then SUM(budget)-SUM({<Period_Date={"=$(v_prevmonth)"}>}budget) but it's not working right because the second part returns the same value as the first

[thi_pham · Accepted solution]

As you use Above function in measure Sales, why don't you use it in Budget as well:

 sum(Budget) - Above(sum(Budget))

[Enriquemmesteo]

That totally worked!

I was trying so many ways and none was working.

Thank you very much 😀

[thi_pham]

I'm glad it helps