Forum Discussion

Icon for Advocate II rank

Advocate II

2 years ago

Solved

Create a Column with a Measure as Dates comparision

Hi!

I created a Measure in this way, by getting the values of Date after selecting it in a Slicer:

SelectedDate = SELECTEDVALUE('Date'[Date])

then i want to create a column the told me if the "SelectedDate" is between two given dates, ad I use this:

Data1 = IF(AND('Table'[StartDate]<='Table'[SelectedDate],'Table'[EndDate]>='Table'[SelectedDate]),1,0)

but i get only 0, even if there are some cases in which the formula must give back1.

I checked that all dates are in the same format.

Any suggestions?

Thanks!

Gabry
2 years ago
Yes this should be good if put on a measure. Doesn't work?

well not properly as you wrote
this should work:
Measure =  VAR DataControllo = SELECTEDVALUE ( 'Date'[Date] )


  RETURN
CALCULATE ( SUM ( Table[Cost] ),
  Table[StartDate] <= DataControllo,
  Table[EndDate] >= DataControllo
)

Sei italiano?

4 Replies

Gabry
Super User
2 years ago
As i know you can't create calculated column based on a measure value that is evaluated on the fly.

You should use a measure and not a calculated column. Calculated columns are evaluated only when you refresh the data
- SebaSpotti
  Advocate II
  2 years ago
  So also this solution is not ok?
  
  SumData1 =
  CALCULATE (
  VAR DataControllo = SELECTEDVALUE ( 'Date'[Date] )
  RETURN
  SUM ( Table[Cost] ),
  Table[StartDate] <= DataControllo,
  Table[EndDate] >= DataControllo
  )
  
  Thanks
  - Gabry
    Super User
    2 years ago
    Yes this should be good if put on a measure. Doesn't work?
    
    well not properly as you wrote
    this should work:
    Measure =  VAR DataControllo = SELECTEDVALUE ( 'Date'[Date] )
    
    
      RETURN
    CALCULATE ( SUM ( Table[Cost] ),
      Table[StartDate] <= DataControllo,
      Table[EndDate] >= DataControllo
    )
    
    Sei italiano?

Related content