How to covert excel formula into Dax

[Anonymous]

Hello All,

I have table as below image.

Table1

I have written formula to get the plansum value for everymonth.

If month value is not available then it should comapre with the previuos month value.

Formula:-

J7

=IF(H7=I7,"",IF(H7/I7>I7,UNICHAR(8679),UNICHAR(8681)))

H7 I7

{=INDEX(Table1[PlanSum],LARGE((ABS(Table1[PlanSum])>0)*ROW(Table1[PlanSum]),1)-(CELL("Row",E2)-1))}

{=INDEX(Table1[PlanPrevMon],LARGE((ABS(Table1[PlanPrevMon])>0)*ROW(Table1[PlanPrevMon]),2)-(CELL("Row",E2)-1))}

So how can i change these formuals into dax.

Any suggestions.

Mohan V

[Anonymous]

I think you are approaching this the wrong way.  Instead, start with the goal you are trying to achieve and look for the best method to do this within Power BI.  In some cases there will be some Excel -> Dax converting you might do, but here it looks like you want to do something that is suited to a completely different approach.

Are you able to write in a paragraph what you are attempting to achieve?

[Anonymous]

Thanks for the replay @Anonymous , @dkay84_PowerBI.

I was just trying whether it works with excel or not.

But as you guys are saying that it is not suited with excel then i will go with the complete powerbi approach.

Actually 

I have created the custom kpi using below measure.

PlanArrows = IF(ISBLANK([PlanSum]),"No Data Available ",[PlanSum])&" "&IF([PlanSum]=[PlanPrevMon],"",IF([PlanSum] > [PlanPrevMon],UNICHAR(8679),UNICHAR(8681))&IF([PlanSum]<=0,"",""))

And it works fine for me for perticular month which i choose from chicklet slicer of months.

But  here i dont want user to choose the month values from slicer.

I would like to show the card values as 

if current month value is not availble then it should compare with the previous month value automatically.

like

In above image Apr-2017 value is not available then it should compare with the Mar-2017 value.

If Mar-2017 value also not available then the previous mont value.Keep on goes

Any suggestions.

Mohan V

[Anonymous]

The idea is possible, but will require an overhaul.  Slicer work by limiting the data your dax expressions see.  If you select April, March data is essentually hidden.

If you to do what you are talking about, you would need to set up a date table that is not linked to the rest of your data tables.  This table would hold current month and previous month.  These dates are linked to your slicer only, then your DAX expressions will need to run their own CALCULATE functions to filter the data dynamically, trying to mimic what the slicer was previously doing.  That Calculate function would need to be run by looking up the Date table (which your slicer has reduced) looking for a current month or previous month value.

Your CALCULATE functions would get this months value and previous months value.  You can then have a 3rd measure which is an "If 'Measure 1' > 0 then 'Measure 1' else 'Measure 2'" style measure.

[Anonymous]

Wow that was very quick @Anonymous.

Actually I am just started to learn all these Dax and powerbi.

And onemore thing is I dont want to use the slicer anymore. Actually I am not going to use the slicer.

I just want to show the month comaprision in a card visuals.

Can you please share an example that how exactly it works.

I ll be soo thankful to you...

[Anonymous]

CALCULATE is a Dax formula you will use often, so learning it early will help you out.  It essentually runs a DAX expression, but with additional filters.  It will include any filters already on your data so we can use this to achieve your idea.

So the date table.  There are numous ways to create a 'Dim - Date' table but here is one i like:
http://www.agilebi.com.au/power-bi-date-dimension/

The first problem we will need to figure out a solution to will be the "Last Month" column you will need to add.  We you need to do is add a column and work out a formula that will get you to the date of the 1st of the previous month.  There could be a whole post on solving that, but you might find another thread that already gives you that answer.

Ok so assuming we have managed to get the table we want.  We need to create our measures.  First will just create the Current month measure.  We simply take your existing measure and put it inside a Calculate Statement:

PlanArrows = CALCULATE(
	IF(
		ISBLANK([PlanSum]),
		"No Data Available ",
		[PlanSum]
	) & " " & IF(
		[PlanSum]=[PlanPrevMon],
		"",
		IF(
			[PlanSum] > [PlanPrevMon],
			UNICHAR(8679),
			UNICHAR(8681)
		) & IF(
			[PlanSum]<=0,
			"",
			""
		)
	),
	'TableName'[DateColumn] >= [FILTERDATESTART],
	'TableName'[DateColumn] <= [FILTERDATEEND],
)

All i have done there is spreadout your code so its more readable.  There are 2 lines at end your calculate statement.

'TableName'[DateColumn] >= [FILTERDATESTART],
'TableName'[DateColumn] <= [FILTERDATEEND],

These reference your plan data, so change them to the Tablename and Date column.
They also reference 2 new measures we'll need to create.  I'm going to hit post and write more.

[Anonymous]

So i mentioned [FILTERDATESTART] and [FILTERDATEEND].  Calculate doesn't play well with Measures, so we are going to have to replace all of the measures inside this Calculate with whatever DAX expression we would have used in all places.  This will inlcude things like [PLANSUM] and [PLANPREVMON]

For [FILTERDATESTART] we just need code like:

[FILTERDATESTART] = FIRSTDATE('DimDates'[Date])

[FILTERDATEEND] is the same, but use LASTDATE instead.

If you go through and replace all of the measures in the new formula we created for PlanArrows, you have a measure that calculates the current month.  To make a measure that does PlanArrows Last Month, repeat the same process except the FIRSTDATE and LASTDATE will need to point at the 'DimDates'[LastMonth] date column you created earlier.

Lastly you create the 3rd measure that uses an IF statement.  I've seen that you know them already, so i'll leave you to do that bit. It should be pretty easy.

Sorry this was so long, i'm sure you will have questions.  As you can see, its doable but not as straightforward.

[Anonymous]

One update i need to make to this. "We you need to do is add a column and work out a formula that will get you to the date of the 1st of the previous month."

This will cause 1 problem in some cases (it doesnt in the ones i use due to how our financial data is stored).  If your Plan data is stored in the days 2 through 31, our previous month formula will miss most of it due to how we filtered.  To solve this, take the LASTMonth date, add 1 month and minus 1 day.  That should give you the last day of the month.

[Anonymous]

@Anonymous You have given a lot of stuff to solve this issue.

I did try to understand the whole thing and tried to solve.

First,i would like to give info of what kind of data that i am having.

I am having table's plan rev, actual rev, and MonthYear

I have creatd relashionship using Date column from MonthYear table to plan rev and actual rev Date coloumns.

MonthYear table

And in plan rev, month year, plan rev, date, and in actual rev actual rev, month year, and date columns.

Q1:- for LastMonth  formula that will get the date of the 1st of the previous month.

I did created a cacluated column  to get the previous mont value using

LastMonth = PREVIOUSMONTH('Month Year'[Date])

Output is...

Then if im not wrong, as you have suggested i am getting the 1st date of the previous month.

Please correct me if im wrong.

Then written measures for

FILTERDATESTART= FIRSTDATE('MonthYear'[Date])

FILTERDATEEND= LASTDATE('MonthYear'[Date])

 Then for current month PlanArrows measure is.

PlanArrows = CALCULATE(
	IF(
		ISBLANK([PlanSum]),"No data Available",[PlanSum]
	)& " "& IF(
		[PlanSum]=[PlanPrevMon],
		"",
		IF([PlanSum]>[PlanPrevMon],
			UNICHAR(8679),
			UNICHAR(8681)
		) & IF([PlanSum]<=0,
		"",""
		)
	),
	'Month Year'[Date]>=FILTERDATESTART, 
        'Month Year'[Date]<=FILTERDATEEND
)

But it is not working. Getting the error as

A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

@Anonymous Please correct me.

[Anonymous]

Yes you are on the right track but have hit the wall i expected.  In my subsequent post i wrote "So i mentioned [FILTERDATESTART] and [FILTERDATEEND].  Calculate doesn't play well with Measures, so we are going to have to replace all of the measures inside this Calculate with whatever DAX expression we would have used in all places.  This will inlcude things like [PLANSUM] and [PLANPREVMON]"

Essentially you need to replace the references to measures in your formula, with the actual Dax code.  By formatting the code and spacing it out you'll be able to keep this managable.

[Anonymous]

I did tried the below measure and it worked for me.

PlanArrows = 
VAR s = [LastMonth]
RETURN
    CALCULATE (
        IF ( ISBLANK ( [PlanSum] ) , "No data Available", [PlanSum] ) & " "
            & IF (
                [PlanSum] = [PlanPrevMon],
                "",
                IF ( [PlanSum] > [PlanPrevMon], UNICHAR(9650),UNICHAR(9660) )
                    & IF ( [PlanSum] <= 0, "", "" )
            ),
        'Month Year'[Date] >= s
   
    )

But there are lot more things which are not working as i expected.

Note:- Month Slicer only restricted on Table.

1:-As you can see in above image, I didnt choose any month values.
So PlanArrows1 is another measure  for jus crosschecking that i have written to get the whole sum of month values(Neglect it).

In Table, for May-2017 value is No data available and also From Apr-2017 to Jul-2016 values are 0.
So here by Planarrows measure i was expecting that it should show me Jun-2016 value i.e 4368.

Because i was expecting the output as it should compare with the previous month values and till it gets the value(Not 0 also), it should show me only that perticular month value, not sum of all months.

And you can observe in table that From May-2016 to Jan-2016, for May-2016 value is 4368 but it is giving me sum of that and next month. And Mar to Jan2017 values are not availbale but still showing the 8736 value,

which is not expecting.

2:-

Here you can see for Apr-2017 actually Plansum value is Not available.

But in Planarrows it is showing me 0, which is perfect as per the measure.

But here how can we get the value of Jun-2016 as it is the actaul value apart from all zeros and no data available.

@Anonymous you have given me a lots of stuff to explore.

I am soo thankfull to you.

But i think this is the last query which it will end this thread.

Please dont hesitate to help.

Thanks,

Mohan V

[Anonymous]

So what happens when you select a month?  What is not working as expected?

EDIT: Looks like you editted your response after i posted this.  I'll read through the new stuff.

[Anonymous]

I think the problem here is that PlanSumPrevMonth can only ever be 1 of 2 numbers.  Its either PlanSum or PlanSum last month.  Nothing is rolling your data forward, its simply a switch of this month and last month.  For the method shown, the zeros should be correct.  Having it loop backwards will require another level of complexity.  Probably more complex than i could do for you in this forum.

[Anonymous]

@Anonymous Yes I knew, that it is beyond the expectations from a forum.

But still have some hopes on you that you can suggest me a way to get this done.

Any ideas of something which i can try to solve this.

Please....

[Anonymous]

If i had to search for a solution i'd look for a method of getting the last value in a table.  Take that solution and instead of looking at the whole table, i'd look at the table up to the date prior to the date of the current row.

https://community.powerbi.com/t5/Desktop/How-can-I-get-the-last-value-of-a-column-in-my-dataset/td-p...

This thread has an example that you might be able to make work.

[Anonymous · Accepted solution]

Well I think i found the solution @Anonymous.

I have gone through the link that you have provided and after lots of mess..

I think i got the solution.

Measure is

LastNonBlank Plan = 
var LastNonBlankDate=CALCULATE(MAX('Month Year'[Date]),FILTER(ALL('Month Year'),'Month Year'[Date]<=MAX('Month Year'[Date]) && [PlanSum]<>0))
return
CALCULATE([PlanSum],
	FILTER(ALL('Month Year'),'Month Year'[Date]=LastNonBlankDate)
)

Output is:-

As i expected, i need to show only the last non blank value in cards i think it is giving me perfect output.

Correct me if i am wrong.

And thanks @Anonymous. you have spent a lots of time and you have given me lots of inputs to explore myself.

Keep in touch.

[Anonymous]

Good work.  Glad i could help.

[dkay84_PowerBI]

Why do you need to change to DAX? Your example doesn't indicate that you are working with a data model and DAX isn't really designed for referencing individual cells like your excel formulas.