cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

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

Accepted Solutions
Highlighted
Helper III
Helper III

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

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
Highlighted
Super User I
Super User I

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

@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"
)
)





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Community Support
Community Support

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

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 it as the solution to help the other members find it more quickly.
Highlighted
Helper III
Helper III

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

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

Highlighted
Super User I
Super User I

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

@Johan did you try if isblank?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Solution Sage
Solution Sage

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

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
Highlighted
Helper III
Helper III

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

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.

Highlighted
Helper III
Helper III

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

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)

Highlighted
Solution Sage
Solution Sage

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

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
Highlighted
Helper III
Helper III

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

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

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors