latest Month to last Month data comparison

[Anonymous]

Hello,

i have one scenario where "Info Table" have value for Jan, Feb, March, but we currently are in Oct. 

How can i show a separate column for diff of March and Feb?

and automatically if i get data for April, it will update to be the diff of April - March?

i have a "calendar table" connecet with this " Info Table" by dates.

Please let me know if you want me to clarify more. thanks in advance!

Br,

RW

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.

[Anonymous]

Hello Yadong Fang!

Thanks for your exmaple, my final goal is to show the diff of latest month in visualization report , like what i present in excel.

Do you know how to create a separate value for "Diff", regardless the column filter (in this case, "Month") ? Thanks again!

[v-yadongf-msft · Accepted solution]

Hi @Anonymous ,

Please create three new columns:

Jan = IF('Fact Table'[Month] = 1, 'Fact Table'[Value])

Feb = IF('Fact Table'[Month] = 2, 'Fact Table'[Value])

Mar = IF('Fact Table'[Month] = 3, 'Fact Table'[Value])

Create a table visual:

You can rename field for table visual like below:

Best regards,

Yadong Fang

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

[v-yadongf-msft]

Hi @Anonymous ,

You'd better transform data like below in Power Query firstly:

You will get a table like this:

Relationship between Calendar table and Fact table:

Try following DAX to create two columns:

Previous Month = IF('Fact Table'[Month] = MAX('Calendar Table'[Month]),CALCULATE(MAX('Fact Table'[Value]),FILTER('Fact Table','Fact Table'[Month] = MAX('Calendar Table'[Month])-1 && 'Fact Table'[ID] = EARLIER('Fact Table'[ID]))))

Diff = IF('Fact Table'[Month] = MAX('Calendar Table'[Month]), 'Fact Table'[Previous Month] - 'Fact Table'[Value])

You will get the result you wnat:

Best regards,

Yadong Fang

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