Forum Discussion

Dvilches_'s avatar
Dvilches_
Frequent Visitor
5 years ago

EARLIER FUNCTION

Hello everyone,

 

I hope you're doing great. I made a column calculation (in FacSales table) that identifies in wich date a company started working with us trough this expresion: 

 
Customer Initialization = CALCULATE(MIN(DimCalendar[Date]),FILTER(FacSales,FacSales[RUT] = EARLIER(FacSales[RUT])))
 
So far so good, but now i would like:
 
  1.  Try to do this expresion but now in a measure.
  2. Due that my report is made with months views, i would like just to see the new clients that appear in the current month. All the dates of the incorporations of the companies are shown, but my intention is only to show the one of the month that I have filtered in the slicer. I don't know very well how to do this since the relationship is between the DimCalendar date and FacSales through the [Fecha_OT] field and not between the "Customer Initialization" field, which is the column I created earlier.

Any recommendation will be well received.

 

Thak you very much.

 

 

Diego V.

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    You should never, ever create a calculated column in a fact table using CALCULATE unless the fact table is really tiny. On real fact tables it's going to take a lot of time to calculate. Very often it just leads to a crash due do the amount of memory needed.

     

    [Cust Init Date] =
IF( HASONEVALUE( DimCustomer[CustomerID] ),
    CALCULATE(
        MIN( FactSales[Fecha_OT] ),
        ALLEXCEPT(
            FactSales,
            DimCustomer
        )
    )
)
    • Dvilches_'s avatar
      Dvilches_
      Frequent Visitor
      5 years ago

      Thanks for the feedback daxer, i got same result. Now how can i get just dates depending on selected value in slicer? (Using something like ALLSELECTED?)

    • Dvilches_'s avatar
      Dvilches_
      Frequent Visitor
      5 years ago

      Daxer, i think that i found the way using another measuere that uses your recomended measure:

       

      Measure = CALCULATE([Cust Init Date], FILTER(FacSales, MONTH(FacSales[Fecha_OT]) = MONTH([Cust Init Date])))
       
      So thank you again.