Can anyone provide a simple formula that works?

[lcasey]

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.

 

[BhaveshPatel]

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" ))

[parry2k]

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

[lcasey]

hMMM, I dont see the files..   Should there be a link?

[parry2k]

interesting not letting me attach the file, i can send it by email.

[lcasey]

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. 

[parry2k]

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.

[lcasey]

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....

[parry2k · Accepted 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

[lcasey]

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!!

[lcasey]

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!

[parry2k]

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

[lcasey]

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?                                                                                               

[alanhodgson]

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

[lcasey]

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:

[alanhodgson]

@lcasey,

Interesting... Try this instead:

= CALCULATE(SUM('Table'[Column]), PREVIOUSYEAR('Table'[Date Column]))

[lcasey]

Nope,

That doesnt even give me a balance.

[parry2k]

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.

[lcasey]

I think Microsoft is restricting the Power BI forums or made some sort of change,

Attachments and replying to private messages no longer working.

[parry2k]

I believe this is also resolved and we can successfully close it, let me know if need further help.

Thanks,

P

[lcasey]

Here is my email address:  lcasey@techmillennium.com

I cant even reply to your Private Message...

Thanks!!!