topic Re: Correctly calculate last 12 month value from YTD in DAX Commands and Tips

Correctly calculate last 12 month value from YTD

pawelj795 — Wed, 08 Jan 2020 14:59:08 GMT

Hi,
I have calculated Inventory Value (only YTD) for every month in 2018 and 2019.
For example, in end of January 2018 we have 1 mln EUR, in end of February 2018 1,5 mln EUR etc...

Now, I need to sum inventory value from last 12 month for every month in 2019.
Is it possible to take value for particular month, e.g. January 2018 and sum with February January 2018 etc?

In other words, 01.18 - YTD Val - 1 mln, 02.18 - YTD Val-2 mln, ...., 12.18- 1,5 mln.
And I want to sum it.

Maybe use SELECTED VALUE or something like that?

Re: Correctly calculate last 12 month value from YTD

JarroVGIT — Wed, 08 Jan 2020 19:09:47 GMT

Hi pawelj795 ,

Consider everyone in this community completely unaware of what you are working on 🙂 What do you mean by 'i have calculated inventory value' -> is there a table? did you create a Matrix visual? Are you talking about measures?

In short:

1. what does you current dataset look like and is it possible to share sample data?

2. What measures do you currently have?

3. What does the desired output look like? (do you want a calculated table, or a visual? or even just a measure?)

Thanks

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂

Re: Correctly calculate last 12 month value from YTD

pawelj795 — Thu, 09 Jan 2020 08:29:23 GMT

JarroVGIT
Haha, I realized that now, I was writing very fast.

Maybe, I try the simpliest way to clarify.
Below, I have YTD value calculated for every month.
Is it possible to create measure which sum YTD values from last 12 months?
Furthermore, it should calculate for every month.
Example:
For 01.2020 - it should SUM whole 12 months of 2019

For 12.2019 - it should sum from January to November 2019 and December 2018

For 11.2019 - it should sum from January to October 2019 and November 2018 and December 2018

etc..

The main issue is to how sum YTD values.

Re: Correctly calculate last 12 month value from YTD

pawelj795 — Thu, 09 Jan 2020 10:30:33 GMT

Re: Correctly calculate last 12 month value from YTD

pawelj795 — Thu, 09 Jan 2020 14:43:58 GMT

So, I suppose it is not possible?

Re: Correctly calculate last 12 month value from YTD

JarroVGIT — Thu, 09 Jan 2020 15:57:37 GMT

Hi pawelj795 ,

It is very much possible but we just have to little information. You show a table with 'monthnumber' but we (nor would a measure) would know what year we are looking at. So again, I ask my question: what does your datamodel look like or is it possible to share a sample pbix with dummy data?

(Just fyi, my responsetimes are mostly after work and usually within 24 hours, replying every few hours to your own thread won't do you any good and clutters the topic.)

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂

Re: Correctly calculate last 12 month value from YTD

pawelj795 — Fri, 10 Jan 2020 08:21:46 GMT

JarroVGIT
The table shows YTD inventory values for 2019 year.

Below my sample data
https://drive.google.com/open?id=1wx1wlefLOXW8ZLl6_jRyHN9wvdAnFGdm

To summarize, I want to sum YTD values.

I think, the measure I am looking for, should somehow treat YTD value for every month as usual value and then I would be able to sum them.

Re: Correctly calculate last 12 month value from YTD

JarroVGIT — Fri, 10 Jan 2020 14:29:46 GMT

Hi pawelj795 ,

When looking at your sample data, I don't see YTD values but point-in-time values. Just to be clear (because you contradict yourself in your posts):

- You have point in time quantities in your datamodel (not YTD values):

- Per month (seperated by year I assume?), you want to see the SUM of YTD values of the 12 previous months?

- Do you agree that a "YTD value" of the month September 2019 is the SUM of all quantities from January 1st 2019 till September 30th 2019?
The question that you are asking is implies the following: if you look at March 2019, we are summing the following numbers:

1. YTD March (which is January 2019 + February 2019 + March 2019 quantities)

2. YTD February (which is January 2019 + February 2019 quantities),

3. YTD January (which is January 2019 quantities)

4. YTD December (which is January 2018 + February 2018 + March 2018 + April 2018 + May 2018 <every month> + December 2018 quantities)

5. YTD November (which is January 2018 + February 2018 + March 2018 + April 2018 + May 2018 <every month> + November 2018 quantities)
<same for October, September, August, July and June>

11. YTD May (which is January 2018 + February 2018 + March 2018 + April 2018 + May 2018 quantities)

12. YTD April (which is January 2018 + February 2018 + March 2018 + April 2018 quantities)

I am just asking this to be sure because it seems like a useless number but maybe you do have a real use case for this 🙂

One final question: are you using a datetable?

Re: Correctly calculate last 12 month value from YTD

pawelj795 — Fri, 10 Jan 2020 14:47:37 GMT

JarroVGIT
Yes of course, I have date table.
QTY in this case are usefull, I forgot to delete them.

I also realized that I named whole case incorrectly.
It shouldn't be YTD values but Inventory Value from last 12 months cumulatevily.

But sum from end of every month should be sumed.
Example;
Calculation for November 2019 ->
Cumulative value from November 2018 + Cumulative value from December 2018 + Cumulative value from January 2019 + .... + Cumulative value from October 2019

I have real case to create this whole thing 🙂

Re: Correctly calculate last 12 month value from YTD

JarroVGIT — Sat, 11 Jan 2020 09:17:17 GMT

Hi pawelj795 ,

I now understand what you are trying to achieve. Since summing cumulatives is the same as summing the parts of the cummalitives, your measure is like this (I created my own date table called DimDate with a relationship to your table).:

Sum of prior 12 months = VAR _dateEnd = MIN(dimDate[Date]) - 1 VAR _dateStart = DATE(YEAR(_dateEnd) - 1, MONTH(_dateEnd), DAY(_dateEnd)) RETURN CALCULATE(SUM(Sheet1[Inventory Value EUR]), dimDate[Date] > _dateStart && dimDate[Date] <= _dateEnd)

When this is evaluated, variable _dateEnd looks at the current set of dates (for March in a table visual, this is 01/03/2019 till 31/03/2019) and takes the MIN of that (01/03/2019) and subtract 1 day (28/02/2019). Variable _dateStart takes dateEnd and subtracts 1 year (=28/02/2018). Then we sum all InventoryValues for the dates that are larger than _dateStart (so 01/03./201😎 and later) and earlier then or on 28/02/2019.

When this measure is evaluated in a Table visual (where the months are coming from the DateTable!), the results are like this:

I think you severly overcomplicated your case by looking for summing a cummulative of the past 12 months seperately while if you think about it, you just want the cumalitive of the past 12 months. Also, it really didn't help by misnaming the whole case indeed with YTD terms 😉

Anyway, I hope this solves your case! Let me know if it works, kudo's are appreciated to show support.

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂

Re: Correctly calculate last 12 month value from YTD

pawelj795 — Tue, 14 Jan 2020 10:04:24 GMT

JarroVGIT
Unfortunately, we still far away from desired solution 😞

Maybe, this will help you understand my problem.

Measure I'm looking for should Treat Value at the end of month (which was calculated incrementally) as individual values and sum them from last 12 months.