SQL statment

[Liklikadze]

Hi i'm trying to implement this Sql statment in power bi  SUM(amount * coeff) where tdate < calendar[date] 

So basicly amount, coeff and tdate are in one table and caledar[date] is another table no luck in querry edittor and also in creating new mesure.

[v-eachen-msft · Accepted solution]

Hi @Liklikadze ,

If you want a column to do it, you could refer to the following DAX:

Column = 
'Table'[amount]*'Table'[coeff]

Then create relationship between this table and calendar table based on date column.

Now you could use SUM() function or set it in the field to get the result. And you could use date as a slicer to filter the data.

[amitchandak]

what is expected value of calendar date when you want to say tdate < calendar[date] 

For measure

calculate(SUMX(table,table[amount] *table[coeff]),filter(table,table[tdate] < max(calendar[date] ))