Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Mat84
Frequent Visitor

Power BI Previous Month Total Saving per Categories

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
3 REPLIES 3
v-yuezhe-msft
Microsoft Employee
Microsoft Employee

@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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

@Mat84,

Please check DAX in the following PBIX file.

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

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors