Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

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

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:

ChemicalSubstanceNameValueValidFrom
CS_TEST_252021-10
CS_TEST_24.5342022-02
CS_TEST_22.936622022-06

 


Output data :

ChemicalSubstanceNameValueValidFrom
CS_TEST_252021-10
CS_TEST_252021-11
CS_TEST_252021-12
CS_TEST_252022-01
CS_TEST_24.5342022-02
CS_TEST_24.5342022-03
CS_TEST_24.5342022-04
CS_TEST_24.5342022-05
CS_TEST_22.936622022-06
CS_TEST_22.936622022-07
CS_TEST_22.936622022-08
CS_TEST_22.93662until 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

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.