Create a new measure to sum the balance for 1 year prior to the month end date shown.

[jamoroso]

I have a table that contains month end dates and balances. I am trying to create a calculation that will sum the balance for the month end date of the prior year.  I am using this formula, but the leap year is causing a problem. 

Balance Minus 1 Year = CALCULATE(SUM(spDateIssue[Balance]),DATEADD(spDateIssue[Month End Date],-1,YEAR),spDateIssue[Is Last Day Of Month] = "Y")

[Anonymous]

Hi

The problm is in your date in 2020 feb had 19 days and 28 days in 2021.

If you want to erase this, just add a format yearmonth  instaed of date

[jamoroso]

Sample data

Process Date	Month End Date	Is Last Day Of Month	Balance
20200131	1/31/2020	Y	192711525
20200131	1/31/2020	Y	1259970
20200131	1/31/2020	Y	59902154
20200131	1/31/2020	Y	91966623
20200131	1/31/2020	Y	130970662
20200229	2/29/2020	Y	196948547
20200229	2/29/2020	Y	95719878
20200229	2/29/2020	Y	131051430
20200331	3/31/2020	Y	62478737
20200331	3/31/2020	Y	94619356
20200331	3/31/2020	Y	131394540
20200430	4/30/2020	Y	209600995
20200430	4/30/2020	Y	1047838
20200430	4/30/2020	Y	69637474
20200430	4/30/2020	Y	102866879
20200430	4/30/2020	Y	130335372
20200531	5/31/2020	Y	107221236
20200531	5/31/2020	Y	213551528
20200531	5/31/2020	Y	129734649
20200630	6/30/2020	Y	220754032
20200630	6/30/2020	Y	954008
20200630	6/30/2020	Y	107819169
20200630	6/30/2020	Y	75255419
20200630	6/30/2020	Y	127671475
20200731	7/31/2020	Y	115195034
20200731	7/31/2020	Y	631396
20200731	7/31/2020	Y	77014490
20200731	7/31/2020	Y	226001367
20200731	7/31/2020	Y	126292139
20200831	8/31/2020	Y	116343068
20200831	8/31/2020	Y	524547
20200831	8/31/2020	Y	230046669
20200831	8/31/2020	Y	76031535
20200831	8/31/2020	Y	124870937
20200930	9/30/2020	Y	119664720
20200930	9/30/2020	Y	360662
20200930	9/30/2020	Y	75133540
20200930	9/30/2020	Y	232674996
20200930	9/30/2020	Y	120538223
20201031	10/31/2020	Y	236677751
20201031	10/31/2020	Y	78441424
20201031	10/31/2020	Y	219970
20201031	10/31/2020	Y	126434076
20201031	10/31/2020	Y	118017584
20201130	11/30/2020	Y	126980247
20201130	11/30/2020	Y	78460453
20201130	11/30/2020	Y	88822
20201130	11/30/2020	Y	236974429
20201130	11/30/2020	Y	116588377
20201231	12/31/2020	Y	80927497
20201231	12/31/2020	Y	26263
20201231	12/31/2020	Y	133475995
20201231	12/31/2020	Y	241234046
20201231	12/31/2020	Y	114435771
20210131	1/31/2021	Y	79914875
20210131	1/31/2021	Y	26329
20210131	1/31/2021	Y	136067676
20210131	1/31/2021	Y	249111722
20210131	1/31/2021	Y	111937984
20210228	2/28/2021	Y	252325671
20210228	2/28/2021	Y	80861261
20210228	2/28/2021	Y	26389
20210228	2/28/2021	Y	139580067
20210228	2/28/2021	Y	110114488
20210331	3/31/2021	Y	17550
20210331	3/31/2021	Y	88474912
20210331	3/31/2021	Y	149182672
20210331	3/31/2021	Y	266396028
20210331	3/31/2021	Y	106357416
20210430	4/30/2021	Y	271915683
20210430	4/30/2021	Y	8229
20210430	4/30/2021	Y	91682271
20210430	4/30/2021	Y	152282365
20210430	4/30/2021	Y	105707734
20210531	5/31/2021	Y	151162903
20210531	5/31/2021	Y	91438807
20210531	5/31/2021	Y	273260827
20210531	5/31/2021	Y	104364038
20210630	6/30/2021	Y	276002296
20210630	6/30/2021	Y	91912580
20210630	6/30/2021	Y	150299761
20210630	6/30/2021	Y	102722913
20210731	7/31/2021	Y	97995686
20210731	7/31/2021	Y	100748355
20210831	8/31/2021	Y	286424400
20210831	8/31/2021	Y	97859893
20210831	8/31/2021	Y	153400185
20210831	8/31/2021	Y	99344982
20210930	9/30/2021	Y	154857831
20210930	9/30/2021	Y	96331724
20210930	9/30/2021	Y	294254407
20210930	9/30/2021	Y	98461533
20211031	10/31/2021	Y	296652899
20211031	10/31/2021	Y	94736494
20211031	10/31/2021	Y	159661438
20211031	10/31/2021	Y	97867538
20211130	11/30/2021	Y	95582473
20211130	11/30/2021	Y	157880092
20211130	11/30/2021	Y	299822025
20211130	11/30/2021	Y	96483406
20211231	12/31/2021	Y	96224363
20211231	12/31/2021	Y	307948796
20211231	12/31/2021	Y	95223107
20220131	1/31/2022	Y	144925859
20220131	1/31/2022	Y	92839931
20220228	2/28/2022	Y	324623608
20220228	2/28/2022	Y	148156989
20220228	2/28/2022	Y	104584597
20220228	2/28/2022	Y	91429390

[tackytechtom · Accepted solution]

Hi @jamoroso ,

Thanks for sharing the data. I am not sure whether this is the easiest way, but would this one solve your issue?

Balance using same period Tomstry = 
VAR _currentMonth = MONTH ( MAX ( Table[Month End Date] ) )
VAR _previousYear = YEAR ( MAX ( Table[Month End Date] ) ) - 1
RETURN
CALCULATE ( 
    SUM ( Table[Balance]), 
    REMOVEFILTERS ( Table[Month End Date] ),
    MONTH ( Table[Month End Date] ) = _currentMonth,
    YEAR ( Table[Month End Date] ) = _previousYear
)

Here the result:

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[Abh_90]

I have actually the same task to do, these solution is working to get the measure for Febreary balance but it gave a wrong Total (the last period [december] instead of the SUM of all period) as you see in your screenshot !!
I tried using TOTALYTD instead of SUM but still give me the same result !
Any idea please?

[jamoroso]

Sorry, I am new to Power BI and do not know how to do this. I am using a stored procedure and am doing a right click, select New Measure and type the calculation in. How do I enter all of the above info? Thanks, Janet

[tackytechtom]

Hi @jamoroso ,

Don't you worry! We all have been there 🙂

Right click on the table or folder you'd like to create your measure in and choose measure:

Then there is this white box apeparing where you can paste the code in:

You probably need to align the table names and columns so they fit your model.

I am not sure what you mean by Stored Procedure since I just know them from SQL Server...

Hope this helps!

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[jamoroso]

Thank you! This worked. 

[tackytechtom]

Hi @jamoroso ,

Have you tried to use SAMEPERIODLASTYEAR() instead of DATEADD()?

Here a link to MS documentation:

SAMEPERIODLASTYEAR function (DAX) - DAX | Microsoft Docs

Let me know if this helps 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[jamoroso]

Yes, I tried this and I get the same issue with Feb. 

Balance using same period = CALCULATE(SUM(spDateIssue[Balance]),SAMEPERIODLASTYEAR(spDateIssue[Month End Date]),spDateIssue[Is Last Day Of Month] = "Y")