cancel
Showing results for
Did you mean:
Member

## Can anyone provide a simple formula that works?

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: Can anyone provide a simple formula that works?

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

Proud to be a Datanaut! Kudos Help
Connect with me on Linkedin. Feel free to email me with any of your BI needs.

20 REPLIES 20
Member

## Simple formulas dont work

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?

Established Member

## Re: Simple formulas dont work

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

Member

## Re: Simple formulas dont work

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:

Super User

## Re: Can anyone provide a simple formula that works?

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

Proud to be a Datanaut! Kudos Help
Connect with me on Linkedin. Feel free to email me with any of your BI needs.

Established Member

## Re: Simple formulas dont work

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

## Re: Can anyone provide a simple formula that works?

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

Super User

## Re: Can anyone provide a simple formula that works?

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

Proud to be a Datanaut! Kudos Help
Connect with me on Linkedin. Feel free to email me with any of your BI needs.

Highlighted
Member

## Re: Simple formulas dont work

Nope,

That doesnt even give me a balance.

Super User

## Re: Simple formulas dont work

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.