Calendar function on a date column in Text datatype in a direct query connection

Hi Anonymous 

Since the DirectQuery mode has a lot of limitations, if possible, please convert to the Import mode.

First of all, convert the ValidFrom column to date type, and add a calulated column to get the next date:

NextDate =
IF (
    CALCULATE (
        MIN ( 'Table'[ValidFrom] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[ValidFrom] > EARLIER ( 'Table'[ValidFrom] )
        )
    )
        = BLANK (),
    TODAY (),
    CALCULATE (
        MIN ( 'Table'[ValidFrom] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[ValidFrom] > EARLIER ( 'Table'[ValidFrom] )
        )
    )
)

Then create a new table:

_Outcome =
VAR _DateTable =
    ADDCOLUMNS (
        CALENDAR ( MIN ( 'Table'[ValidFrom] ), TODAY () ),
        "YearMonth", FORMAT ( [Date], "YYYY-MM" )
    )
RETURN
    SUMMARIZE (
        FILTER (
            CROSSJOIN ( 'Table', _DateTable ),
            [Date] >= [ValidFrom]
                && [Date] < [NextDate]
        ),
        'Table'[ChemicalSubstanceName],
        [Value],
        [YearMonth]
    )

 The result is as follow:

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.