difference between to month cumulative (date is not available as date are number)

[phaum1967]

Hi, My table is like that. 

I create 2 measures:

SumXAmount:=SUMX(reel;reel[Montant])

and

var_Amount:=var previous = CALCULATE([SumXAMount];filter(All(reel[NumberMonth]);reel[NumberMonth]=MAX(reel[NumberMonth])-1)) return  [SumXAMount]-previous

with var_Amount, I m able to do a var in a pivot table only if I use as column: Numbermonth.

But I want to have this var every time I m doing a pivot table (even if I don t have Numbermonth as column

for exemple, If I add libMois as column, my var is not working (values are sum of last month).

thanks for help

[Jimmy801]

Hello @phaum1967 

I'm not an DAX-expert but to me it seems that max-formula is not placed in the right context. Without having your datamodel, I can't test and it's a blind guessing. But try something like this

var_Amount:=var previous = CALCULATE([SumXAMount];filter(All(reel[NumberMonth]);reel[NumberMonth]=MAX(EARLIER(reel[NumberMonth]))-1)) return  [SumXAMount]-previous

If this post helps or solves your problem, please mark it as solution.
Kudos are nice to - thanks
Have fun

Jimmy

[phaum1967]

Hi Jimmy,

I tried your solution but I ve an error saying :

Max function accepts only a unique column reference as argument.

Any idea ?

thanks

[phaum1967 · Accepted solution]

Hi,

I find a way to solve my issue by working with data before import in power pivot. 

I'm using this method... 

https://exceleratorbi.com.au/use-power-query-compare-database-records/

[dax]

Hi phaum1967,

You could add a custom column like below in M code, then use this in var.

= Table.AddColumn(#"Changed Type", "Custom", each if [month name] = "Jan" then 1 else if [month name] = "Feb" then 2 else if [month name] = "Mar" then 3 else null, type number)

Or you also could convert date Jan 2019 like below by change type to Locale in M code like below(you need to change type to localtion which use your month format)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8krMU9IBkQoKRgaGlkqxOtFKbqlJQDEgiRDyTSwCCgFJqFAsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"month name" = _t, y = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"month name", type text}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Custom", each if [month name] = "Jan" then 1 else if [month name] = "Feb" then 2 else if [month name] = "Mar" then 3 else null, type number),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Added Conditional Column", {{"y", type date}}, "af-ZA")
in
    #"Changed Type with Locale"

Then create a calendar table by DAX, Table 2 = CALENDARAUTO(), create relationship between date in two table, then you could use below measure to get previous month value. You could refer to PREVIOUSMONTH  for details.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.