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
Johan
Advocate II
Advocate II

accumulated value not shown if there is no entry in the month

Hi,

 

I have this dax formula to accumulate the value over time periods:

 

amount accum. = calculate(sum(fact[amount]), filter(all(periods);Periods[Period] <= max(Periods[Period])))

 

it calculates correctly, but if I put it in a matrix table it does not show a value if there is no entry in the period.

So, it shows like this:

 

Account 201701 201702 201703 201704 Total
1          500           800   1000   1000
2         1000   1200          1800   1800
3          600    700                  700
Ttl       2000   2300   2700   3500   3500

 

How to get a value in each cell, also for periods where there is no entry on the account?

 

 

Thanks,

Johan

1 ACCEPTED SOLUTION

I understand the method you're using. Dates would be better then period numbers.

Still for the accumulated (running total) function it should not differ whether it's a date or any sequential number.

 

I've been playing around with an extreme simple example and found out it has to do with the relationship type. If I set it to both it only shows the numbers in the period / months with entries, if I put it to single it sets the value in all periods. I've no clue why, not sure if it's a bug or feature. 

 

pbi test running total.jpg

View solution in original post

15 REPLIES 15
sdjensen
Solution Sage
Solution Sage

Hi @Johan,

 

What kind of value is 'Period' in your 'Periods' Table? Is it your YearMonth value or is it a date?

 

If you have a Year column and a date column in your periods table you could try with a formula like this:

CALCULATE(
    SUM('fact'[amount]),
    FILTER(
        ALL( 'Periods'),
        'Periods'[Year] = MAX( 'Periods'[Year] ),
            && 'Periods'[Date] <= MAX( 'Periods'[Date] )
    )
)

 

I am not sure how your data is structures, but you should have a real date in your fact and all the dates in your Periods table between the 1st of january in the first calendar year represented in your fact table to the highest date represented in your fact table - and then the date column in each table should be the relationship.

 

I don't know if it's intended, but if your fact has values for 2016 your formula would accumulate across the years.

/sdjensen

Thanks. 

I think this reply relates to my other forum question 'show value of last year'. Your way works indeed. The period table is different then a calendar, since there can be a period 13 at the end of the year for closing the balance sheet/income statement. Also entry dates of march can still belong to period 2 (feb)

what you describe to me with none standard calendar "months" makes it even more important with the date coloums.

 

The formula I gave you is my standard formula to calculate fiscal amount year to date for none standard calendars. In my demo model the fiscal year start 1st of juli.

 

Below you can see the result of the my formula. I know there are still empty cells but this is because the YTD value is actually 0, but notice how the values in Jan, Feb and Mar 2017 is identical because there has been no postings in these periods in my demo data.

FiscalYTD.png

 

The way I would handle your year end postings is to more these from period13 to period12 and then add an extra coloum in your fact where you mark these with a 1 (0 for period1-12) - this way you can still handle these in your formulas.

 

The formula would be able to handle if you e.g only had 10 days in period 1 and 40 days in period 2 (I know this is not what is going on, but just to explain what happens). What is does is that inside each 'cell' it calculates the highest date and then return the sum of all the values for this year until this date.

/sdjensen

I understand the method you're using. Dates would be better then period numbers.

Still for the accumulated (running total) function it should not differ whether it's a date or any sequential number.

 

I've been playing around with an extreme simple example and found out it has to do with the relationship type. If I set it to both it only shows the numbers in the period / months with entries, if I put it to single it sets the value in all periods. I've no clue why, not sure if it's a bug or feature. 

 

pbi test running total.jpg

calerof
Impactful Individual
Impactful Individual

Hi  @Johan, and supporting team @sdjensen@v-shex-msft@vanessafvg

 

Did you find the answer to your question? I have the same problem. This is my table by date and hour with blanks after exporting it to excel (Single tab):

Acumulado transacc 2019_03.xlsx

 

When I export it to excel and manually calculate the cumulative value, the correct result is shown in the Acum tab from the same file above.

 

But PBI instead shows the cumulative values in column AB from the Acum tab from the excel file. 

 

This is the image of the actual output:

 

PBI cumm matrixPBI cumm matrix

This shows that on Saturday March 3, 2019 from 19:00 to 23:00 there were no entries and my cells are blank, and the cumulative measure is not bringing the entries for those hours from the previous day, March 2, 2019, i.e.

 

Hour1920212223
3/2/19470415269368284

 

that add 1,806, the difference I have in the calculated cumulative matrix. And so on for all saturdays with blank entries, referencing end of shift I suppose.

 

My formulas for counting entries are:

 

Num Transacc = COUNT( table[Index] )
And to accumulate the transactions:
 
Num Transacc Acum = CALCULATE(
                                            [Num Transacc],
                                            FILTER(
                                                 ALLEXCEPT( 
                                                        table, 
                                                        table[Pistola], 
                                                        table[ZONA_TRAB] ),
                                                        table[FECHA] <= MAX( table[FECHA] ) && 
                                                        table[Hora] <= MAX( table[Hora] )
                                             )   
                                    )
I have two filters, working area (ZONA_TRAB) and bar code device type (Pistola). I also have a calendar table related to the main table.
 
A sample data file with 10 top & bottom lines from the original 800k+ is below:
To the original data file I added and index in the query editor.
 
Any advise anyone?
 
Thanks,
 
Fernando

Hi @Johan,

 

When you setting cross filter direction option to single, it means the filter affect only works on one side. For other side, it not contains the filter affect.

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Thanks for explaining. But why should setting this option effect the way running totals are displayed? It makes no sense to may that when relationship type is 'both directions', that the value of the dax formula is not calculated (or presented). 

@Johan - the simple explanation to why you see a difference in the behavious between having single or both directions is that they filter the data in your fact table differently - if they did the same there would be no need for having both options.

 

I will try to explain what happens in your case. Instead of just having your calendar/period/date table filtering your fact table - your fact table is also filtering your calendar/period/date table and it happens in the context of every 'cell' in your visual. So lets say you have value in your fact table for account 1 in period 1, 2 and 3 - this will calculate your accumulated value as expected, but for account 2 you only have values in your fact table in period 1, and 3 - so in your table in the 'cell' for account 2 / period 1 (and 3) the calculation works as expected, but no value is returned in the call for account 2 / period 2 because since your fact table is now filtering your date table periods 2 in this table in this context, hence you don't get a value returned from your formula.

 

The real "bug" here is that Power BI in most cases by default make a relationship to 'both'. This option is rarely needed to get what you want hence it should not be the default value. I always change all my relationships to single and only use both when needed.

/sdjensen

Thanks for explaining. I understand there is probably a technical reason behind it. But for an enduser it's difficult to explain.

 

I worked with Qlikview for a couple of years and there all relations are both ways. Still it's able to present accumulated values, also when there is no entry in a dimension.

 

Linking tables both ways is a great feature for data exploring. A traditional flow might go from dimension to fact, but it's also interesting to select a customerid in the fact and to see in which months (dimension) the customer did transactions.

 

A solution might be to merge everyting into 1 big fact table.

 

 

 

@Johan,

 

Either I totally missunderstand what you want or I don't see any problems with single direction relationships from you fact to you dimension tables. Having a fact table with a single direction relationship to a customer table and a date table would give you exactly what you ask.

 

If you have a visual showing sales per month you can add a filter with customer 1 and the visual will then only show you this customers sales split my month

 

 

/sdjensen
v-shex-msft
Community Support
Community Support

Hi @Johan,

 

You can try to use below measure to get the running total:

 

Total = if(SUM('sample'[Amount])=SUMX(ALL('sample'),[Amount])||SUM('sample'[Amount])=SUMX(FILTER(ALL('sample'),[Date]=MAX([Date])),[Amount]),
	SUM('sample'[Amount]), 
	SUMX(FILTER(ALL('sample'),[Date]<=MAX([Date])&&[Account]=MAX([Account])),[Amount]))

1.PNG

 

Notice: I haven't found a way to calcualte the running column total, it show the current total.


Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Thanks Xioaxin for your efforts, but still it show blank columns as in your example

@Johan did you try if isblank?





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




yes, I did. Thanks for the suggestion. However it's still not showning a value in periods where there is no entry in the fact.

vanessafvg
Super User
Super User

@Johan

maybe if you force a 0 for blank rows using an if(isblank)

 

eg.

 

Appointments - Assessment Only =
IF (
ISBLANK (
CALCULATE (
[Appointments],
Appointments[AppointmentPurpose] = "Assessment Only"
)
),
0,
CALCULATE (
[Appointments],
Appointments[AppointmentPurpose] = "Assessment Only"
)
)





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




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.