Power BI Previous Month Total Saving per Categories

[Mat84]

Hi Guys,


Good day to you all. I am very new to Power and have been struggling to write a queries to calculate previous month, current month and YTD based on financial year, April to March period.

The date column is set to first of every month as they have been aggregated to the specific month.

See my sample below:

Date	Categories	TotalSpend	TotalSavings
01/04/2013	xxxx Loan	718606.75	119767.8333
01/05/2013	xxxx Loan	718606.75	119767.8333
01/06/2013	xxxx Loan	718606.75	119767.8333
01/07/2013	xxxx Loan	718606.75	119767.8333
01/08/2013	xxxx Loan	718606.75	119767.8333
01/09/2013	xxxx Loan	718606.75	119767.8333
01/10/2013	xxxx Loan	718606.75	119767.8333
01/11/2013	xxxx Loan	718606.75	119767.8333
01/12/2013	xxxx Loan	718606.75	119767.8333
01/01/2014	xxxx Loan	718606.75	119767.8333
01/02/2014	xxxx Loan	718606.75	119767.8333
01/03/2014	xxxx Loan	718606.75	119767.8333
01/04/2014	xxxx Loan	1063629.417	261004.25
01/05/2014	xxxx Loan	1063629.417	261004.25
01/06/2014	xxxx Loan	1063629.417	261004.25
01/07/2014	xxxx Loan	1063629.417	261004.25
01/08/2014	xxxx Loan	1063629.417	261004.25
01/09/2014	xxxx Loan	1063629.417	261004.25
01/10/2014	xxxx Loan	1063629.417	261004.25
01/11/2014	xxxx Loan	1063629.417	261004.25
01/12/2014	xxxx Loan	1063629.417	261004.25
01/01/2015	xxxx Loan	1063629.417	261004.25
01/02/2015	xxxx Loan	1063629.417	261004.25
01/03/2015	xxxx Loan	1063629.417	261004.25
01/04/2015	xxxx Loan	1227346.75	424368.5
01/05/2015	xxxx Loan	1227346.75	424368.5
01/06/2015	xxxx Loan	1227346.75	424368.5
01/07/2015	xxxx PSA	2916667	833333.3886
01/07/2015	xxxx Loan	1227346.75	424368.5
01/08/2015	xxxx PSA	2916667	833333.3886
01/08/2015	xxxx Loan	1227346.75	424368.5
01/09/2015	xxxx PSA	2916667	833333.3886
01/09/2015	xxxx Loan	1227346.75	424368.5
01/10/2015	xxxx PSA	2916667	833333.3886
01/10/2015	xxxx Loan	1227346.75	424368.5
01/11/2015	ICT Services  for Test	285639.64	28563.954
01/11/2015	xxxx PSA	2916667	833333.3886
01/11/2015	xxxx Loan	1227346.75	424368.5
01/12/2015	xxxx PSA	2916667	833333.3886
01/12/2015	xxxx Loan	1227346.75	424368.5
01/01/2016	ICT Services  for Test	138840.88	13884.078
01/01/2016	xxxx PSA	2916667	833333.3886
01/01/2016	xxxx Loan	1227346.75	424368.5
01/02/2016	ICT Services  for Test	361052.75	36105.265
01/02/2016	xxxx PSA	2916667	833333.3886
01/02/2016	xxxx Loan	1227346.75	424368.5
01/03/2016	ICT Services  for Test	176458.48	17645.838
01/03/2016	xxxx PSA	2916667	833333.3886
01/03/2016	xxxx Loan	1227346.75	424368.5

Thank you in advance.

Mat

[v-yuezhe-msft]

@Mat84,

Firstly, create a calendar table following the guide in this blog, then create relationship using date field of calendar table and date column of your original table.

Secondly, to calculate YTD based on your fiscal year, please create the following measure.

FYTD Measure =
TOTALYTD ( SUM ( Table[YourColumn] ), 'Calendar'[Date], "03/31" )

Thirdly, create the following current month measure and previous month measure.

Current Month Measure = CALCULATE(
SUM(Table[YourColumn] ),
FILTER(
'Calendar',
'Calendar'[Month]=MONTH(NOW())
&& 'Calendar'[Year]=YEAR(Now()) )
)

Previous Month  = CALCULATE(SUM(Table[YourColumn]), PREVIOUSMONTH('Calendar'[Date]))

Regards,
Lydia

[Mat84]

Thanks Lydia for the prompt response.

The previous Month did not quite work. Basically, I want the previous financial Month total sum. For example, in my case, My financial Month starts in April to March. So,  Month 12 is March and April is Month 1. As May is the current Month it corresponds to Month 2 in the financial period. Does the PreviousMonth Function takes account of this. When I manually sum the previous month total, it about 5 thousands but the functions is giving me about 20 thousands.

Any idea how this might work giving the above scenario. Thanks

mat

[v-yuezhe-msft]

@Mat84,

Please check DAX in the following PBIX file.

https://1drv.ms/u/s!AhsotbnGu1Nok25FIfLLiFgfUZB-

Regards,
Lydia