Forum Discussion

juliausha's avatar
juliausha
Helper I
5 years ago
Solved

Difference in running total

I need to calculate Differece and % difference in my running total per month: 

That's how I get Running total number: 

 

Running total = 
CALCULATE(
	DISTINCTCOUNT('Logged in per month'[TravellerId]),
	FILTER(
		ALLSELECTED('Logged in per month'[Month]),
		ISONORAFTER('Logged in per month'[Month], MAX('Logged in per month'[Month]), DESC)
	)
)

 

Do you know how to get the last 2 columns? 

Thanks! 

 

 

Need Help

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    juliausha , if you can create date from month then with help from date table you can measure like

     

    MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
    last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
    previous month value = CALCULATE(sum(''Table''[total hours value]),previousmonth('Date'[Date]))

    diff = [MTD Sales]-[last MTD Sales]

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    5 years ago

    juliausha - See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339586.
    The basic pattern is:
    Column = 
      VAR __Current = [Value]
      VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])
      VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
    RETURN
      __Current - __Previous

  • FrankAT's avatar
    FrankAT
    Community Champion
    5 years ago

    Hi juliausha 

    what about using Power Query like this:

     

    // Table
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8krM0zcyUNJRMjUwUIrViVZyS02CCJhBBXwTi6ACphABxwKogDlcRSVUwAIi4FUKNdTSGCaQAxEwNIDqcSxNh4oYgoyNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Month = _t, #"Running total" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Month", type date}, {"Running total", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Previous", each try #"Added Index"[Running total]{[Index]-1} otherwise [Running total]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Difference", each [Running total] - [Previous]),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "%Difference", each try [Difference] / [Running total] otherwise null),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom2",{{"%Difference", Percentage.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Index", "Previous"})
in
    #"Removed Columns"

     

    Don't bother with date format it's localized for me.

    With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
    FrankAT (Proud to be a Datanaut)