Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

filter with datesbetween

hi all,

 

i am having an issue with creating a table for filter, i believe i am missing something that causes the formula to fail.

sample data (date column is proper date in powerBI, below just for understanding):
date | profit
1jan | 10
2jan | 20
3jan | 30
4jan | 40

what i want to achieve is to add profits from 4jan to 2jan, and i have written a measure below, but it keeps failing - with error showing ' a table of multiple values was supplied where a single value was expected '

any help is much appreciated.

latest P&L =
CALCULATE( SUMX( table, [profit] ),
var starting= DATEADD( [date] , -2 , DAY)
return
FILTER(
    enfusion_position_table, DATESBETWEEN([date] , starting , MAX([date]))
    )
    )



Need Help
Show and Tell

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    Anonymous , with help from date table

    example

    Rolling 3 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-3,day))

     

    rolling Days Formula: https://youtu.be/cJVj5nhkKBw