Convert SQL statement to Power bI

[ApurvaKhatri]

I need to convert this query in sql to power bi

  select sum([lMoney) FROM Money
  where (fromdate between '2017-09-02' and'2017-09-30') and (todate between '2017-09-08' and '2017-09-29' )

Is It possible ?

I have two columns to date ,from date  I need to calculate monthly data (sum[money])  i.e Sept 2017 based on single date column but filtered on from date 2017-09-01 and to date 2017-09-30. How do i acheive that?

[BILASolution]

Hi @ApurvaKhatri

I don't understand much, but I hope this script helps. --> Query

First, you need to attach AdventureWorks DW. --> AdventureWorksDW

Then you can adapt to your case. (At the end, whenever you've created the view, use it in power bi)

[mow700]

Check out the CALCULATE() function in the DAX reference:

https://msdn.microsoft.com/en-us/library/ee634825.aspx

Essentially you would build something like this:

New Measure = CALCULATE(SUM([Money]), DATESBETWEEN([Fromdate],"2017-09-01","2017-09-30"), DATESBETWEEN([Todate],"2017-09-08", "2017-09-29"))

[ApurvaKhatri]

I need the sum of money for every month  from firstdate - from date to lastdate (todate) of month.

for e.g Sept 1 to sept 30 but i need to select dates based on from date and to date column resp.

[ApurvaKhatri]

I need to have data from every month, so I need to generialize it

[mow700]

Easy to do, just update the filters in your calculate statement for your scenario.  These filters don't have to be hard coded, in fact I almost always calculate these.  How would you do this in your original T-SQL statement?  DAX can likely emulate your approach. 

For example, check out the DAX filter functions like ALLSELECTED():

https://msdn.microsoft.com/en-us/library/ee634807.aspx

[ApurvaKhatri]

Please can you help me with the code that what should I replace with in place of dates.

I am unable to do it

[mow700]

Your original TSQL statement doesn't give me enough details to help here, the example is using hard coded dates.  DAX can certainly help you determine these dates, but you'll need to provide additional details.  

[ApurvaKhatri]

I am unable to do it.