Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Cakos
Frequent Visitor

Cumulation other months

Hey everyone,

 

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

 

Monthsingle valueaccumulated value
January1,091,09 
February1,452,54 
March1,33,84
April2,15,94

 

Many thanks!

 

19 REPLIES 19
Anonymous
Not applicable

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

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!

Anonymous
Not applicable

// 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

D

 Hey @Anonymous ,

 

thx for your reply.

 

I tried it and this was the result:

 

Bild1.jpg

 

Any other ideas?

 

Stay healthy!

Anonymous
Not applicable

I tried it and it works very well.

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

Best
D

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):

Bild1.png

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

Bild3.JPG

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:
 

Bild1.jpg

"Soll without adj" is calculated as follows:

 

Bild4.JPG

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

Anonymous
Not applicable

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

@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?

Anonymous
Not applicable

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

Best
D

Like this?

 

Bild5.JPG

This does not work either.

 

The problem is here:

Bild6.JPG

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

Anonymous
Not applicable

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

Anonymous
Not applicable

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
Anonymous
Not applicable

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

Best
D

Ok, I try to put it in chronogically:
 
AR 2018.JPG
AR 2019.JPG
Average AR 2018.JPG
IC AR 2018.JPG
IC AR 2019.JPG
AR per month 2019.JPG
Soll without adj.JPG
AR Rest 2018_1.JPG
AR Rest 2018_2.JPG
 
AR Rest 2018_help.JPG
 
Measure.JPG
 
Accumulated Measure.JPG
 
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
Anonymous
Not applicable

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

Ok, thank you!

 

Try this link: 

https://we.tl/t-mPNaEdvvH9

 

Kind regards!

Anonymous
Not applicable

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
Anonymous
Not applicable

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
Anonymous
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors