Calculate running total for missing values, showing blank

[Anonymous]

Hi there, 

I am building a report to represent the cumulative of a column (the cashflow) over time (different years) based on some categories (i.e. different asset_types). The problem is that not all the years have an equivalent value and consequently these years are shown blank in my bar chart. I was wondering if someone let me know how to fix my DAX query.

3-Cumalative Cash Flows =

CALCULATE (sum (Output_SSIS [Cash Flow]),

filter(

ALLselected (Output_SSIS),

Output_SSIS [Financial Year] <= Max (Output_SSIS [Financial Year])

),VALUES(Output_SSIS[Asset type])

)

-- This is shown as the value for each column

Total Cumalative Cash Flow = CALCULATE( sum(Output_SSIS[Cash Flow]),filter(ALLSELECTED(Output_SSIS),Output_SSIS[Financial Year]<=Max(Output_SSIS[Financial Year])))

This is shown as line on top of the chart

The orange category (asset_type) does not have any record(cash flow) for the last three years of the x-axis and that is the reason it is shown as blank.

Any help is highly appreciated.

Thanks

[Anonymous]

This is what happens when you build a model that does not follow Best Practices, that is, the star schema. Please build a correct model and your problems will be gone. Here's something to get you started: https://docs.microsoft.com/en-us/power-bi/guidance/star-schema

Here's why you've got problems of this kind. You've only got one big table, Output_SSIS, and for some dates some types are not present in it. Therefore, when you start filtering, some values will be missing in the context. In your case it's VALUES(...) that is causing the problem (in fact, you're causing the problem, not the function). If you use a proper dimension for Asset Type, this problem will immediately be gone since choices made in dimensions stay put regardless of what's in the fact table. Please therefore structure your models correctly in the first place.

[Anonymous]

@Anonymous 

Hi there, thanks for the link, I never thought these issues can be fixed with an appropriate data model. I was always seeing these concepts in the field of data model not DAX queries. The link looks very interesting, I will go through this (and the if clause) over the next few days and report back. 

[Anonymous]

@Anonymous

If you think about it deeply, it only makes sense that your graph looks the way it does. Think about the financial year where you don't see the values. You don't see them because in your formula you put VALUES(Output_SSIS[Asset type]). But for these years, when they are visible in the context, there are NO asset types, so your filter is empty in these years and this, of course, affects all the years that are filtered in your formula, hence your measure must return nothing. This all stems from the fact that you're taking all values from the fact table (one-table models should never be used in professional settings, only star schemas). If you had a dimension with your types, your selected type(s) (say, via a slicer, for instance) would be visible all the time regardless of other choices elsewhere. Hence, your formula would work OK without even putting VALUES(...) in the formula. Just stick to correct models and you'll be safe.

[Anonymous]

Hi @Anonymous  ,

Thanks again for the explanation and also the link. It was very educational,.

I have formed a star schema by breaking my big table into smaller ones by some DAX expressions such as 

Dim_Asset_type = DISTINCT(Output_SSIS[Asset Type])
 
So the whole * schema is shown below:

I put some filtering options below my chart which are coming from these dimensions and I use them to filter the chart values.

But still do not know how to modify my cumulative and total value (also a dax expression) to use these filters. The Y axis shows incorrect values, repetetive values, I guess it is because of the "allselected" function in the dax, please see below

Now my cumulative is :

Cumulative Cash Flow = CALCULATE (sum (Output_SSIS [Cash Flow]),filter(ALLSELECTED(Output_SSIS),Output_SSIS [Financial Year] <= Max (Year_Table[Financial Year])))

It shows the year correctly (i.e. no missing values) but it does not bring the correct values as I explained earlier. I appreciate that if you could advise.

and another try:

Cumulative Cash Flow = CALCULATE (sum (Output_SSIS [Cash Flow]),filter(ALLSELECTED(Output_SSIS),Output_SSIS [Financial Year] <= Max (Year_Table[Financial Year])),FILTER(Output_SSIS,Output_SSIS[Asset Type]=ALLSELECTED(Dim_Asset_type[Asset Type])),FILTER(Output_SSIS,Output_SSIS[Project Type]=ALLSELECTED(Dim_PrjType[Project Type])),FILTER(Output_SSIS,Output_SSIS[Precinct]=ALLSELECTED(Dim_Precinct[Precinct])))

And another try

Cumulative Cash Flow = CALCULATE (sum (Output_SSIS [Cash Flow]),filter(ALLSELECTED(Output_SSIS),Output_SSIS [Financial Year] <= Max (Year_Table[Financial Year]) && Output_SSIS[Asset Type] in ALLSELECTED(Dim_Asset_type[Asset Type]) && Output_SSIS[Project Type] in ALLSELECTED(Dim_PrjType[Project Type]) && Output_SSIS[Precinct] in ALLSELECTED(Dim_Precinct[Precinct])))

 I think I have fixed that, finally !!, I had to put values at the end, so the code is :

Cumulative Cash Flow = CALCULATE (sum (Output_SSIS [Cash Flow]),filter(filter(ALLSELECTED(Output_SSIS),Output_SSIS[Financial Year] <= Max (Year_Table[Financial Year])), Output_SSIS[Asset Type] in ALLSELECTED(Dim_Asset_type[Asset Type]) && Output_SSIS[Project Type] in ALLSELECTED(Dim_PrjType[Project Type]) && Output_SSIS[Precinct] in ALLSELECTED(Dim_Precinct[Precinct])),VALUES(Dim_Asset_type[Asset Type]))

But if this is the solution, can you explain why the values should be at the end, it seems it select distinct values but sitll cannot figure it out why I need to enforce it

Thanks

[amitchandak]

@Anonymous ,

Try like

CALCULATE (sum (Output_SSIS [Cash Flow]),
filter(
ALLselected (Output_SSIS),
Output_SSIS [Financial Year] <= Max (Output_SSIS [Financial Year])
)
)

or

CALCULATE (sum (Output_SSIS [Cash Flow]),
filter(
ALLselected (Output_SSIS),
Output_SSIS [Financial Year] <= Max (Output_SSIS [Financial Year]) && Output_SSIS[Asset type] = max(Output_SSIS[Asset type])
)
)

[Anonymous]

@amitchandak 

Thanks Amit, but none of them works

and the second one

I think I need to modify the calculation somehow to force it to use Output_SSIS [Financial Year]-1 if isblanck(Output_SSIS [Financial Year])  with some if statements possibly with some variables, but not sure exactly how but working on it