CONVERT SQL QUERY TO DAX Measure

[Anonymous]

Hi ,

Please Help me .

I want to convert sql  qury into  DAX Measure 

-----------SQL Query -------------------------

DECLARE @DATE AS DATETIME SET @DATE='200-12-03'

select SUM(Account_Balance) FROM DepDeposit WHERE As_Of_Date=dateadd(DD, -1, dateadd(YY,datediff(yy,0,@DATE),0))

[vanessafvg]

@Anonymousare you trying to get last years values?

do you have a date table?  if so you can use

daxmeasure = calculate(sum(account_balance)), SAMEPERIODLASTYEAR(DateTime[DateKey]))

[Anonymous]

Dear vanessafvg,
thanks for your answer.

yes I want to get last years values
but the resultes show with me not the same sql query resulte , also i don't have date table, how i can cearte **bleep** and what are the values must be inside table to apply your answer ,

kindly find snapshop image after applying your answer without date table , i use only my attribute called "as_of_date

"

[vanessafvg]

@Anonymous you have to have a continious date range to use the function, therefore you need a data table

you can use this script to create a date table

www.mattmasson.com/2014/02/creating-a-date-dimension-with-a-power-query-script/

then link the date to the date in  your table

then create the measure

[Anonymous]

Dear vanessafvg,
thanks for your answer.

Can You guid me  how I can create date table  in  power  bi and  where I can paste Script ?

Best Regards, 

[shebr]

Hi,

Please see this great video of scripting a Power BI date table.

https://youtu.be/vJ7xNUK8sQE

Thanks

[vanessafvg]

@Anonymous into a blank page into power query

if you read the post i posted it gives you all the details, please read it

[Anonymous]

Dear vanessafvg,

thanks alot ,
the creation of table done and make relation between date table and my Measure table , but the the resulte is wrong, please finde bellow iamges

[vanessafvg]

@Anonymous you might need to format your result, it usually displays like that when a number is to long, what is the data type?  can you show the result in a table to see the result properly

[Anonymous]

hi ,

yes I format my resulte, but I want to show  the  same  number  in  sql qury ,like  the  query  in bellow  image  

so  my problem only  how to  convsert where condition "inside blue line" form sql into Dax