Month to month comparison

[Yuiitsu]

Dear All

Please see if the following can be achieved in powerbi.

(I am unable to attach Powerbi files or box link here so please see a sample table.)

#1 Month to month comparision

Every biweekly the sales will amend the forecast for the year, and the results are merged in powerquery.

Below is a simplified sample of how the table looks like after power query merged all the individual files together.

Date	Booking Amount	BU
11-Jun-24	25,641,561	CT
11-Jun-24	251,548	AS
11-Jun-24	548,846	AS
11-Jun-24	25,186	AS
11-Jun-24	163,845	CT
26-Jun-24	29,641,961	TH
26-Jun-24	291,949	CT
26-Jun-24	949,946	AS
26-Jun-24	29,196	TH
26-Jun-24	163,949	TH
26-Jun-24	99,946	AS
26-Jun-24	949,946	AS
12-Jul-24	59,196	CT
12-Jul-24	163,949	TH
12-Jul-24	25,641,561	AS
12-Jul-24	59,196	TH
12-Jul-24	163,949	CT
25-Jul-24	949,946	TH
25-Jul-24	59,196	CT
25-Jul-24	163,949	AS
25-Jul-24	25,641,561	TH
10-Aug-24	949,946	CT
10-Aug-24	59,196	TH
10-Aug-24	163,949	AS
10-Aug-24	25,641,561	TH
22-Aug-24	52,946	TH
22-Aug-24	949,346	AS
22-Aug-24	59,196	CT
22-Aug-24	163,949	CT
22-Aug-24	25,645,461	CT

What I want to do, is to compare month to month what is the increase or decrease in the forecast.

I should use only the SUM of the latest booking amount of the month but

I am not able to do it as Powerbi will SUM the entire month booking amount which is not accurate.
What I want is to take only the sum of the latest date of the month and SUM the total to compare month to month.

Date	Sum of Booking Amount
26-Jun	32,126,893
25-Jul	26,814,652
22-Aug	26,870,898

Last Mth_HQ BK Amt = CALCULATE(
    [Total_HQ BK Amt],PARALLELPERIOD('Date_Key'[Date],-1,MONTH))

This measure will not work. Please help me to see how I can get the result? 

( I also would like to do Year to Year comparison ! It would be helpful if you can also give tips for this)

[Anonymous]

Hi All
Firstly , BIswajit_Das thank for you solution!
And @Yuiitsu  for your question,as I understand it, you have two requirements, one is to count the sum of the latest date of each month and the second one is to get the sum of the previous month for forecasting.

We'll start by creating a calculated column to filter the table by year and month to get our latest values for each month.

LatestDateInMonth = CALCULATE(MAX('Table'[Date]),ALLEXCEPT('Table','Table'[Date].[Year],'Table'[Date].[Month]))

And then create a new table to count the sum of them, which will be used to match the visual objects you send out.

A = SUMMARIZE('Table','Table'[Date],"LatesBookingAmount",
CALCULATE(SUM('Table'[Booking Amount]),FILTER('Table','Table'[Date]='Table'[LatestDateInMonth])
)
)

Create an index column for each month to get the total value for the previous month.

Date index = MONTH('A'[Date])

DaLastMonthBookingAmount = 
IF(
   CALCULATE(MAX(A[LatesBookingAmount]),FILTER('A','A'[Date index]=EARLIER('A'[Date]))),
 0,
   CALCULATE(MAX(A[LatesBookingAmount]),FILTER(A,'A'[Date index]=EARLIER('A'[Date index])-1)
))

If you have any other questions, you can check the pbix file I uploaded, it might help you to solve the problem.

Hope it helps!

Best regards,

Community Support Team_ Tom Shen

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

[Yuiitsu]

Thank you @Anonymous  for your detailed solution.

I cannot seem to fit it inside my original pbix

One of the reason I believe could be because my pbix is much more complicated, as I have multiple raw files and I have star schema linking the report month date columns to a date key.

When I tried to create the same column I get this result. 

It is due to my lack of knowledge and experience I could not apply what you provided for me.

Is it possible or is there any way I could upload my sample pbix here? 

I believe that would help alot.

And I am so sorry for the lack of information in my question post.

[Anonymous]

Hi @Yuiitsu ,

I'd like to help you out as soon as possible as well, if you don't know how to upload attachments, here's the document I found for you, I hope it helps to help you out with uploading attachments.

How to Get Your Question Answered Quickly - Microsoft Fabric Community

Hope it helps!

Best regards,

Community Support Team_ Tom Shen

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

[Yuiitsu]

Thank you. I will see what I can do.

Right now there is too many security block in my company PC there is no way I can link anything to let anyone outside of my organisation to access. I will have to find a way to share using my personal PC.

Meanwhile I will work on other issues on hand.

Thank you again!

[Anonymous]

Hi @Yuiitsu ,

Has your problem been solved after all this time, or has a new problem arisen, if there are any other questions on this issue, feel free to contact me and I'll get back to you as soon as I receive the message.

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

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

[Yuiitsu]

Hi @Anonymous 

I am not able to work on this recently. For now I would like to close this case and come back again next time.

Thank you for checking and your kindess is really very appreciated!

[Anonymous]

Hi @Yuiitsu ,
Thank you for your busy schedule can take the time to reply to me, if you next time to send a case, I can also be at your service at any time, I hope you can accept the solution to solve this case, for you to solve the problem, I feel very honoured!

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

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

[BIswajit_Das]

Hello @Yuiitsu 
Can you Explain it by an example
like
what's the data is like and
what's your required result table look like ..