If there is no data for the new month, add data from the last month

[piotrgrendus87]

Hello.
I would like to know if it is possible to create a column or measure that if there is no new data for a new month then to enter the last data that is, and in case the data is added is to add new updated data. 

In my table I have a column with dates, but for each unit there is not the same month closing period. Some units send the report quarterly and some send the report monthly.
In addition, I have prepared a column with a formula that filters last R12 ( R12 Filter )

if Date.From(Date.AddMonths(DateTime.LocalNow(),-1))>= [Date] and [Date] > Date.From(Date.AddMonths(DateTime.LocalNow(),-13)) then "R12" else null

and i have it this view:

The goal is to create a view like the following images:

In red the latest data
In blue, data copied from Jun 2022, as there are no newer

If there is new data in October then for the month of October, you will see new values

Regards Piotr.

[KT_Bsmart2gethe]

Hi   ,  

There are multiple ways to achieve the outcome:

No code method:

1. Create a complete date table (i.e. 2022-01, 2022-02 .....);
2. Merge your main table with the date table;
3. Sort by date;
4. Apply the fill down function.

Hybrid: 

1. Create a calendar table:= List.Distinct(List.Transform({Number.From(#date(2022,1,1))..Number.From(#date(2022,12,1))}, each Date.ToText(Date.From(_),"yyyy-MM")))
2. Go to the main table, add a custom step:Table.Combine( { Table.FromList( List.Difference(Calendar,PreviousStep[Date]), Splitter.SplitByNothing(), {"Date"} ), PreviousStep } )
3. Sort the date column
4. apply the fill down function.

Regards

KT

[piotrgrendus87]

Hello.

I used this method and i received this result:

When I execute the fill down procedure I received this and the same value is duplicated several times in the rows:

but if removed duplicated by date column ( 01/08/2022) it is remove all my date in background so out of about 400,000 rows I finally see only 35 rows

and my goal is to see something like in column Date

Regards Piotr.

[KT_Bsmart2gethe · Accepted solution]

Hi @piotrgrendus87 ,

I started with the screenshot below:

(try to use the hybrid method. It's much easier and more efficient. The merge method returns everything, and I missed out on one step, i.e filtering.)

CalendarTable

let
    startDate = #date(2022, 1, 1),  //input your own start date
    endDate = #date(2022, 12, 1),   //input your own end date
    dateList = List.Select(                                             //select only the first date of the month
                List.Transform(                                         //convert number to date
                        {Number.From(startDate)..Number.From(endDate)}, //List of date
                        Date.From
                    ), 
                    each 
                        Date.Day(_) = 1
                )
in
    dateList


MainTable

let
    //data as per screenshot
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Value", type number}}),


    Gap = Table.FromList(
                List.Difference(Date, #"Changed Type"[Date]),           //use the list.difference function to find the gap
                Splitter.SplitByNothing(),                      
                {"Date"}                                                //name the column as per the date column in the main table
            ),
    Combine = Table.Combine({#"Changed Type", Gap}),                    //append the main table with the gap
    #"Sorted Rows" = Table.Sort(Combine,{{"Date", Order.Ascending}}),   //sort the date colummn
    #"Filled Down" = Table.FillDown(#"Sorted Rows",{"Value"})           //fill down to fill the gap
in
    #"Filled Down"

Regards

KT

[piotrgrendus87]

Hi,
I tried this method but i received error message. Are you able to attach a file with this example of how you did it ?
Regards Piotr.

[KT_Bsmart2gethe]

Hi @piotrgrendus87 ,

Please see below link to the workbook:

Solution 

Regards

KT