How to reference a column from a virtual table to be used in a IF statement ?

[Anonymous]

Hello Power BI & DAX community,

I am trying to create a measure that calculates the number of new suppliers for each period based on if the suppliers registration date is within that period or not. For example if a supplier has Registration date 20220120 it should be counted as a new supplier for period 202201 etc.

This is what I have tried:

EVALUATE 
VAR cte_Periods =
    SUMMARIZECOLUMNS (
        Dim_Time[Period],
        "Monthmin", DATE ( YEAR ( EOMONTH ( MAX (Dim_Time[Date]), 0 ) ), MONTH ( EOMONTH ( MAX (Dim_Time[Date]), 0 ) ), 1 ),
        "Monthmax", MAX (Dim_Time[Date] )
    )
RETURN
    SUMX (
        SUMMARIZECOLUMNS (
            Purchase[Supplier number],
            "Reg date", MIN(Purchase[Registration date])
        ),
        IF (
            AND (
                [Reg date] >= SELECTEDVALUE ( cte_Periods[Monthmin] ),
                [Reg date] <= SELECTEDVALUE ( cte_Periods[Monthmax] )
            ),
            1,
            BLANK ()
        )
    )

But complains that it cannot find table 'cte_Periods'. How can I apply the virtual cte_Periods table to be used in the return statement ?. I would assume that I need somekind of cross apply or cross join ?

I solved it like this in SQL:

WITH cte_Periods
AS (SELECT TIM.Period, 
           DATEFROMPARTS(YEAR(EOMONTH(MAX(TIM.Date))), MONTH(EOMONTH(MAX(TIM.Date))), 1) AS Monthmin,
           CONVERT(DATE, MAX(TIM.Date)) AS Monthmax
    FROM [dbo].[Dim_Time] AS TIM
    GROUP BY TIM.Period)
SELECT COUNT(DISTINCT CASE
                    WHEN CONVERT(DATE, CONVERT(VARCHAR(8), F.[Registration date]))
                         BETWEEN p.Monthmin AND p.Monthmax THEN
                        F.[Supplier number]
                    ELSE
                        NULL
                END
            ) AS [Number of New Suppliers],
       p.Period
FROM [dbo].[Purchase] AS F
    CROSS JOIN cte_Periods AS p
WHERE F.[Purchase date] >= p.Monthmin
      AND F.[Purchase date] <= p.Monthmax
GROUP BY p.Period;

 
Best regards,
Rubrix

[lbendlin]

Virtual tables don't have a selected value. Use an aggregation that makes sense for your scenario, like MINX(cte_periods,[Monthmin]).