Running Total and Null Values

[troyhimes]

Running Total and Null Values xlsx file 

None of the previous answers to previous questions regarding the same subject seemed to fit my circumstances.  I'm sure it is related to the filter context of my measures, but I don't see it.

The MTD values (BTZ RMO MTD) show up correctly when there is a null value for the day (BTZ RMO) in the first table, but as soon as a sum value of a separate calculated column (sum of MMP BTZ Oz MMP) is added the MTD values become null.  I require the 3rd column for a graph.  Any suggestions?  A side note...I don't fully understand why the individual daily values (BTZ RMO) aggregate correctly (Grand Total = 5492) doesn't match the individual sums (120+287+83+etc.....=5345)

Table 1

 Table 2

[Anonymous · Accepted solution]

Hi there.

You are getting "strange" results because you are not following Best Practices of dimensional design - easy as that.

If you run this in DaxStudio:

DEFINE
    MEASURE Conccentrator_Final[MTD2] =
        CALCULATE (
            [BTZ RMO],
            DATESMTD( 'Calendar'[Date] ),
            Conccentrator_Final[Feed Oz's_2] > 0
        )
EVALUATE
CALCULATETABLE (
    ADDCOLUMNS (
        VALUES ( 'Calendar'[Date] ),
        "RMO", ROUND ( [BTZ RMO], 0 ),
        "MTD", ROUND ( [BTZ RMO MTD], 0 ),
        "MTD2", Round( [MTD2], 0 )
    ),
    'Calendar'[YYYY-MM] = "2020-02"
)

you'll see that [MTD2] gives you the correct answer. The reason being, [MTD2] uses Calendar to do any time-intel calculations. Such calculations do work correctly ONLY in case you are applying the functions to a proper Date table in the model. Any other column and you're out of luck.

You should strictly follow these golden rules if you want to stay on the safe side:

1. Slicing is only ever done through dimensions.

2. Fact tables' columns are all hidden without exceptions. Only measures can be visible.

3. Time-intel is applied only to proper Date tables.

4. The design must be either a star or a flake schema. Any deviation from this and you're asking for troubles.

6. Bi-directional filtering is used ONLY when strictly necessary. 99% of filtering is one-way only.

Best

D

[troyhimes]

@Anonymous Thanks for the golden rules.  I kept seeing references to DaxStudio in other posts without really understanding it's power, thanks to your answer I was motivated to finally look into it.  Being able to "see" a Dax created table is a HUGE help for a beginner like myself. I found this great guide from Matt Allington (https://exceleratorbi.com.au/getting-started-dax-studio/ ) are there other basic guides or forums that you would recommend that pertain to DaxStudio itself?

Your comment regarding time intel functions and a proper calendar table make sense.  Now the issue is a simpler one....because I'm graphing the data, how do I display null values for any date in the future.  See the table below for March.  I don't want any values beyond "today" (3/4/20) to be displayed on the graph.

[Anonymous]

Here are all the techniques to hide future periods:

https://www.sqlbi.com/articles/hiding-future-dates-for-calculations-in-dax/

As for DaxStudio... I don't know any materials. You can try to use the Help section in DaxStudio itself on the ribbon.

Best
D

[troyhimes]

Thanks for the resource, that solved the issue.

[Greg_Deckler]

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490