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

[Hossam_Elkholy]

Hello everyone,

I am using a direct query connection, and the date column is coming in as a text in "YYYY-MM" format.

Requirement: to get the missing dates in between changing values that occurs in different dates and also fill the dates from the last updated one till today's date.
Example: 
Given data:

ChemicalSubstanceName	Value	ValidFrom
CS_TEST_2	5	2021-10
CS_TEST_2	4.534	2022-02
CS_TEST_2	2.93662	2022-06

Output data :

ChemicalSubstanceName	Value	ValidFrom
CS_TEST_2	5	2021-10
CS_TEST_2	5	2021-11
CS_TEST_2	5	2021-12
CS_TEST_2	5	2022-01
CS_TEST_2	4.534	2022-02
CS_TEST_2	4.534	2022-03
CS_TEST_2	4.534	2022-04
CS_TEST_2	4.534	2022-05
CS_TEST_2	2.93662	2022-06
CS_TEST_2	2.93662	2022-07
CS_TEST_2	2.93662	2022-08
CS_TEST_2	2.93662	until today's date

so I am using the following measure to do so ( it needs alot of enhancments I know but thats what i reached so far) :

Measure 5 =

VAR Cal = CALENDAR(MIN('Table'[ValidFrom]),MAX('Table'[ValidFrom]))

VAR exp = CONCATENATEX(Cal,FORMAT([Date],"YYYY-MM"),"

")

RETURN

exp

the measure results in two issues firstly, the max is the last date even though there are multiple dates in between obviously. So, I need it reccursive so it displays all dates till today's date.

Also it display the result in "YYYY-MM" correctly but reoccurs with the number of days in month, so need the month to display only once.

Thank you so much in advance

[Anonymous · Accepted solution]

Hi @Hossam_Elkholy 

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.