DAX : A way to rollback pre calculated values (cumulative fact table)

[Jame5_Blonde2]

Hi, 

I have a fact table with alot of pre-calculated cumulative totals. I need a DAX measure that calculates the value difference between the Time stamps in the right order. The time can be different (this is based when the job that calculates the values are done.) 

My report is based upon Direct Query so i can´t use Power Query solutions. 

As of the first three rows below it should be 0 for the first row and 10 second row, 10 third and so forth...

When this is solved the plot thickens becuse I also have a lot of dimensions I want to slice and dice these new values. Is this possible?

[Fowmy · Accepted solution]

@Jame5_Blonde2 

I didn't see the 2nd table, it was not visible at first :). Please share the correct table structure, yes, granularity matters.

If you are satisfied with my answer, please mark it as a solution so others can easily find it.

Don't forget to give KUDOS ? to replies that help answer your questions

Subscribe to ExcelFort: Learn Power BI, Power Query and Excel

[v-shex-msft]

HI @Jame5_Blonde2,

#1, I think Fowmy's solution is good enough for your requirements, you can use it to accurately extract the values of different timestamps.

#2, Since these values are calculated in measure formula which calculates result will dynamic changes based on its row content.
If you want to do aggregate with these formula results, you need to manually create a variable table to fix the timestamp in specific date ranges you selected and keep the measure result value static. Then you can use iterator functions to apply the second aggregate mode on these fixed variable table records.

Measure Totals, The Final Word  

Sample: apply two-level 'sum' aggregates on measure formula in a select data range.

measure =
VAR selected =
    ALLSELECTED ( Calendar[Date] )
VAR summary =
    SUMMARIZE (
        FILTER ( ALLSELECTED ( Table ), [Date] IN selected),
        [Time],
        "Amount", [Measure]
    )
RETURN
    SUMX ( summary, [Amount] )

Notice: you can change sumx to other iterator functions to use different aggregate mode on the second level. (e.g. averagex, maxx...)

Regards,

Xiaoxin Sheng

[Jame5_Blonde2 · Accepted solution]

Hi,

#1 Yes! I did simplify my dataset and Fowmy´s measure will indeed work when only  "Time" and "Value" is pre-summerized in the dataset.  

#2 This is my full dataset structure (I call this DATA3). I haven´t yet created a separate data table (but will do this now from the distinct "Time" rows) and test your example. And thank you very much for this link article Measure Totals, The Final Word !

Great stuff, and wish me luck. I´ll keep you posted!

[Fowmy]

@Jame5_Blonde2 

Hi,  Use this measure to get the different between current and previous row:

New Value =
VAR _MAx =
    MAX ( DATA3[Time] )
VAR _Date =
    CALCULATE ( MAX ( DATA3[Time] ), FILTER ( ALL ( data3 ), DATA3[Time] < _MAx ) )
VAR _NewVAlue =
    CALCULATE ( MAX ( DATA3[Value] ), DATA3[Time] = _MAx, ALL ( DATA3 ) )
        - CALCULATE ( MAX ( DATA3[Value] ), DATA3[Time] = _Date, ALL ( DATA3 ) )
RETURN
    _NewVAlue

If you are satisfied with my answer, please mark it as a solution so others can easily find it.

Don't forget to give KUDOS ? to replies that help answer your questions

Subscribe to ExcelFort: Learn Power BI, Power Query and Excel

[Jame5_Blonde2]

Thx for the quick reponse but that didn´t really do the trick. Do we need to modifiy it further?

Could it be becuse of the granularity in the dataset seen in my second picture?

[Fowmy · Accepted solution]

@Jame5_Blonde2 

I didn't see the 2nd table, it was not visible at first :). Please share the correct table structure, yes, granularity matters.

If you are satisfied with my answer, please mark it as a solution so others can easily find it.

Don't forget to give KUDOS ? to replies that help answer your questions

Subscribe to ExcelFort: Learn Power BI, Power Query and Excel