Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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.
Solved! Go to 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/
Did I answer your question❓➡️ Please, mark my post as a solution ✔️ |
Also happily accepting Kudos 🙂 |
Feel free to connect with me on LinkedIn! |
#proudtobeasuperuser |
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
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 |
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/
Did I answer your question❓➡️ Please, mark my post as a solution ✔️ |
Also happily accepting Kudos 🙂 |
Feel free to connect with me on LinkedIn! |
#proudtobeasuperuser |
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?
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
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/
Did I answer your question❓➡️ Please, mark my post as a solution ✔️ |
Also happily accepting Kudos 🙂 |
Feel free to connect with me on LinkedIn! |
#proudtobeasuperuser |
Thank you! This worked.
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/
Did I answer your question❓➡️ Please, mark my post as a solution ✔️ |
Also happily accepting Kudos 🙂 |
Feel free to connect with me on LinkedIn! |
#proudtobeasuperuser |
Yes, I tried this and I get the same issue with Feb.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
85 | |
83 | |
66 | |
60 | |
57 |
User | Count |
---|---|
188 | |
111 | |
105 | |
78 | |
71 |