topic Re: Cumulation other months in DAX Commands and Tips

Cumulation other months

Cakos — Thu, 12 Mar 2020 14:34:29 GMT

Hey everyone,

how can I cumulate the single values in my example over months in that way:

Month	single value	accumulated value
January	1,09	1,09
February	1,45	2,54
March	1,3	3,84
April	2,1	5,94

Many thanks!

Re: Cumulation other months

Anonymous — Thu, 12 Mar 2020 20:49:44 GMT

It depends on many things. We don't even know if you're talking about a calculated column or a measure... To be able to accumulate values, you have to have a field that will tell how your entities are ordered. Here your entities are months but they could be anything...

Bset
D

Re: Cumulation other months

Cakos — Fri, 13 Mar 2020 07:51:05 GMT

Hey Anonymous ,

thx for your reply.

I try to put my request into concrete terms:

The column "month" in my example comes from a Date table (Calender with hierarchy) in my model.

"Single value" is a calculated measure.

Do you need more information?

Many thanks!

Re: Cumulation other months

Anonymous — Fri, 13 Mar 2020 14:11:09 GMT

// Base measure of which the accumulated value // is a variation. [Base Value] = SUM( FactTable[Value] ) [Accumulated Value] = var __lastVisibleDate = max( Calendar[Date] ) var __result = calculate( [Base Measure], Calendar[Date] <= __lastVisibleDate, allselected( Calendar[Date] ) ) return __result

Best

Re: Cumulation other months

Cakos — Wed, 18 Mar 2020 08:27:41 GMT

Hey Anonymous ,

thx for your reply.

I tried it and this was the result:

Any other ideas?

Stay healthy!

Re: Cumulation other months

Anonymous — Wed, 18 Mar 2020 08:56:12 GMT

I tried it and it works very well.

https://1drv.ms/u/s!ApyQEauTSLtOgYMyz4VpKYjkORb0gg?e=Zv1cXO

Best
D

Re: Cumulation other months

Cakos — Wed, 01 Apr 2020 09:12:12 GMT

Hello again @all,

hope you're all well.

Unfortunately I still couldn't solve my problem.

Look at this table (all you can see here are measures):

My goal is to accumulate "Measure" in this table column by month. I've already tried the following (DAX formula by Anonymous😞

As I said the result is not what I'm looking for. The DAX formula is the right way to accumulate. I tried it and it works very well with other values. But in this case I've to try something different.

"Measure" is calculated like this:

"Soll without adj" is calculated as follows:

I think here is the problem. Maybe the problem is due to the fact that it is viewed across several years?

Aside "AR Rest 2018" all other measures refer to 2019.

But this is necessary because Measure is a kind of target value related to the previous year.

Would it help if I add an example pbix.-file here?

Thank you again for your help!

Kind regards!

Cakos

Re: Cumulation other months

Anonymous — Wed, 01 Apr 2020 10:17:19 GMT

If you hard-code values into your measures, they will not be working correctly. Measures are to be dynamic by nature and need to adjust to the filters on dimensions.

Best
D

Re: Cumulation other months

Cakos — Wed, 01 Apr 2020 10:38:14 GMT

Anonymous thx for you replay! 🙂

I don't quite understand what you mean.

Where do you see hard-coded values? I think I'm calculating with measures? Or what do you mean?

If what I'm going to do does not work in this way, what kind of other options do I have?

Re: Cumulation other months

Anonymous — Wed, 01 Apr 2020 12:48:32 GMT

You've got measures that in their names have years, 2018, 2019... This means you are hard-coding years into the measures. I strongly suspect this is incorrect as measures should be general and work for any selection of any items from any dimension. I say "suspect" because sometimes (very rarely) there are situations where you want to really hard-code certain values. But I don't think this is the case.

Best
D

Re: Cumulation other months

Anonymous — Wed, 01 Apr 2020 12:50:41 GMT

Try to remove from my measure the allselected(...) bit and instead put in allselected( Calender ). This is what might be causing issues.

Best
D

Re: Cumulation other months

Cakos — Wed, 01 Apr 2020 13:48:57 GMT

Like this?

This does not work either.

The problem is here:

I think Power BI accumulates the 2018 until 2019, but only 2019 should be accumulated in this case.

Measure = Soll without adj - IC AR 2019

Soll without adj = AR per month 2019 - AR Rest 2018

Can we adjust the formula to accumulate only the current year (in this case 2019)?

Kind regards!

Cakos

Re: Cumulation other months

Anonymous — Wed, 01 Apr 2020 14:30:15 GMT

Something's wrong with the measures you're trying to aggregate. Can I see their full definitions, please?

Best
D

Re: Cumulation other months

Cakos — Wed, 01 Apr 2020 15:04:27 GMT

Ok, I try to put it in chronogically:

That are all Measures that I use in this example (aside a Calender DAX formula).

Thank you so much for your time!

Kind regards!

Cakos

Re: Cumulation other months

Anonymous — Wed, 01 Apr 2020 16:40:08 GMT

OK. This does not look right to me. Hard-coding the years into the measures is certainly wrong. Please share the pbix file with me. You can obfuscate the pieces of info that are sensitive.

Best
D

Re: Cumulation other months

Cakos — Wed, 01 Apr 2020 17:24:02 GMT

Ok, thank you!

Try this link:

https://we.tl/t-mPNaEdvvH9

Kind regards!

Re: Cumulation other months

Anonymous — Wed, 01 Apr 2020 19:56:21 GMT

Bear with me, please. I've got a lot of work to do right now but I'll look into this for sure.

Best
D

Re: Cumulation other months

Anonymous — Fri, 17 Apr 2020 12:20:48 GMT

Hi there.

I've been extremely busy lately due to a project I have to finish before I leave the current company. This is why I have not replied until now.

I'll have a look at this today but in about 40 mins I'll have a 3-hour long meeting... Sorry about that.

Best
D

Re: Cumulation other months

Anonymous — Fri, 17 Apr 2020 12:25:24 GMT

Looks like you're asking for a standard year-to-date measure... In such a case, it's much simpler:

// Calender must be THE date table // in the model and marked as such. [Measure YTD] = calculate( [Measure], datesytd( Calender[Datum] ) )

Best
D

Re: Cumulation other months

Anonymous — Fri, 17 Apr 2020 12:27:54 GMT

By the way, you should not have measures in your model that depend on concrete entities like years. Naming a measure [Something 2019] is a sign you are not yet fully conversant with PBI and DAX.

Maybe these could help?

https://www.youtube.com/watch?v=78d6mwR8GtA

https://www.youtube.com/watch?v=_quTwyvDfG0

https://www.sqlbi.com/tv/time-intelligence-in-microsoft-power-bi/

Just a thought...

Best
D