Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Add missing date first day per month

Hi,
I have a table like this:-

But I need to add missing data between month, but only first day from each month which value is the same the last month. Like this:


Is there some way to add the missing data?

Thanks

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous ,

     

    You need to add a new table , please use the following formula to have a try:

    New Table =
ADDCOLUMNS (
    DISTINCT (
        SELECTCOLUMNS (
            CALENDAR ( MIN ( 'Table'[Date] ), TODAY () ),
            "Date", DATE ( YEAR ( [Date] ), MONTH ( [Date] ), 1 )
        )
    ),
    "Value",
        VAR _filldate =
            MAXX ( FILTER ( 'Table', 'Table'[Date] <= EARLIER ( [Date] ) ), [Date] )
        RETURN
            LOOKUPVALUE ( 'Table'[Value], 'Table'[Date], _filldate )
)

    The final output is shown below:

     

    Best Regards,
    Eyelyn Qin
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous ,

     

    You need to add a new table , please use the following formula to have a try:

    New Table =
ADDCOLUMNS (
    DISTINCT (
        SELECTCOLUMNS (
            CALENDAR ( MIN ( 'Table'[Date] ), TODAY () ),
            "Date", DATE ( YEAR ( [Date] ), MONTH ( [Date] ), 1 )
        )
    ),
    "Value",
        VAR _filldate =
            MAXX ( FILTER ( 'Table', 'Table'[Date] <= EARLIER ( [Date] ) ), [Date] )
        RETURN
            LOOKUPVALUE ( 'Table'[Value], 'Table'[Date], _filldate )
)

    The final output is shown below:

     

    Best Regards,
    Eyelyn Qin
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.