Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Ok I am going on 5 hours now and still this DAX language makes me sick!!
There is no possible way to simply calculata an accurate balance with this language. Seriously Microsft dump this crappy language and use a more modern and up to date language for Power BI.!!!
Take a look at this VERY simple table, All I am simply trying to do is have a measure in a column that presents the Balance of all records as of 12/31/2015 so I can compare them agains Balances this year.
So far nothing and I mean nothing , works.
Solved! Go to Solution.
Glad to hear it worked 🙂
I would have posted the formula but wanted to make sure you understand other internals as well, like table relationships and relationship type because I'm sure you will be doing this many times in the future.
Cheers!
Parv
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
You should add the third optional argument in your measure to count the balances as of 12/31/2015.
CALCULATE[Current Balance USD],DATESYTD ( 'Date'[Date], "12-30" ))
See attached pbix and sample excel files, there are calculated measure begins with "m", means measures. I hope it is helpful.
Having relationship between your data and calendar table is important, in my sample file the relationship is on datekey and make sue "cross filter direction" in relationship is set to "Single"
Thanks,
P
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
hMMM, I dont see the files.. Should there be a link?
interesting not letting me attach the file, i can send it by email.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Theres no attachment in the message either. Everything is Broken Today. I guess I have to leave my email here for all to spam.
If you could send the file to : lcasey@techmillennium.com
I just have no way to download an attachment from the private messages.
on your way, I reached out to you in private message in case you want to share your email address in private. anyhow, it is on your way.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Thank You!!!
mPrevious Year = CALCULATE(SUM(Transactions[Receivables Balance(USD)]),ALL('Calendar'), PARALLELPERIOD('Calendar'[Date], -1,YEAR))
I cant beleive this. I litterally woke up at 8:00 am this morning, read every possible article on Previous Balance and your PBIX file provided exactly what I was looking for!!! The numbers are all Correct Now....
This does work....
Glad to hear it worked 🙂
I would have posted the formula but wanted to make sure you understand other internals as well, like table relationships and relationship type because I'm sure you will be doing this many times in the future.
Cheers!
Parv
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Thank you sooo much !!!! Your workbook is exactly what I needed. I think I will be using that workbook more than any other file on my computer!!
Thanks!!!
Ill take a look. If you are interested in making money let me know. I can send cash your way to assit with formulas. Perhaps through Paypal or something.
At this point I dont think DAX is really going to be able to solve our needs but I am willing to pay someone to try!
Hey Casey,
Appreciate the offer but I'm ok to help without any money, let me know what exactly you need, you have my email address, feel free to send the informaiton and I will do my best to get it going.
Cheers,
P
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Hello,
In Excell formulas are super easy but DAX makes no sense at all.
I have a simple table and all I want to do is create a few simple columns. For 3 hours I have been getting nothing but errors!
1st Column = Revalued Receivables Balance Net Of Commission as of 12/31/2015 ( they want to compare the balance as of the whole last year compared to this year) so The column should pick up Everything right up until 12/31/2015 as we are now in 2016. Then in 2017 it should pick up everything up until 2016.
2nd Column = Number of Settlements as of 12/31/2015 This should just count all the Open Invoices as of 12/31/2015
3rd Column = Difference between Revalued Receivables Balance Net Of Commision as of 12/31/2015 and Revalued Receivables Balance Net Of Commision as of 12/31/2016
4th Column = Difference between Number of Settlements as of 12/31/2015 and Number of Settlements as of 12/31/2016
Can anyome figure out what this over complicated DAX language needs to accomplish this?
Hey @lcasey,
There are a few different ways you can do these with a DAX formula. I chose the DATESBETWEEN function because I think it is the easiest to manipulate and understand.
1st column:
= CALCULATE(SUM('Table'[Column]), DATESBETWEEN('Table'[Date Column], "1/1/2015", "12/31/2015"))
2nd Column (assuming you want the count to go up to Today):
= CALCULATE(COUNT('Table'[Column]), DATESBETWEEN('Table'[Date Column], "12/31/2015", TODAY()))
3rd Column:
= (CALCULATE(SUM('Table'[Column]), DATESBETWEEN('Table'[Date Column], "1/1/2015", "12/31/2015"))) - (CALCULATE(SUM('Table'[Column]), DATESBETWEEN('Table'[Date Column], "1/1/2016", "12/31/2016")))
4th Column:
= (CALCULATE(COUNT('Table'[Column]), DATESBETWEEN('Table'[Date Column], "1/1/2015", "12/31/2015"))) - (CALCULATE(COUNT('Table'[Column]), DATESBETWEEN('Table'[Date Column], "1/1/2016", "12/31/2016")))
Hope this helps,
Alan
Hello,
Thanks for assisting. I couldnt even get the 1st column to work. Ideally, this would all be off of report date, but even using a static date doesnt work.
If I set my report date to 12/31/2015 I get correct numbers:
But If I set my report date to the current date the previous year does not show the correct numbers:
Interesting... Try this instead:
= CALCULATE(SUM('Table'[Column]), PREVIOUSYEAR('Table'[Date Column]))
Nope,
That doesnt even give me a balance.
Hey you have another thread in which I mentioned that I have pbix file for you as a sample, unfortunately i cannot attach file with the reply, not sure what is the best way to send it across.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
I think Microsoft is restricting the Power BI forums or made some sort of change,
Attachments and replying to private messages no longer working.
I believe this is also resolved and we can successfully close it, let me know if need further help.
Thanks,
P
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Here is my email address: lcasey@techmillennium.com
I cant even reply to your Private Message...
Thanks!!!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |