cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
lcasey Member
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.

 

 01.png

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Can anyone provide a simple formula that works?

Glad to hear it worked Smiley Happy

 

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





Did I answer your question? Mark my post as a solution.

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




 




20 REPLIES 20
lcasey Member
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                          

 

 

 

01.png

 

Can anyome figure out what this over complicated DAX language  needs to accomplish this?                                                                                               

alanhodgson Established Member
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

Highlighted
lcasey Member
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:

 

01.png

 

 

But If I set my report date to the current date the previous year does not show the correct numbers:

 

02.png

Super User
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





Did I answer your question? Mark my post as a solution.

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




 




alanhodgson Established Member
Established Member

Re: Simple formulas dont work

@lcasey,

 

Interesting... Try this instead:

 

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

Re: Can anyone provide a simple formula that works?

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

 

 

Super User
Super User

Re: Can anyone provide a simple formula that works?

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





Did I answer your question? Mark my post as a solution.

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




 




lcasey Member
Member

Re: Simple formulas dont work

Nope,

 

That doesnt even give me a balance.

 

01.png

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





Did I answer your question? Mark my post as a solution.

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