How to Kepp same value for Friday to Friday

[damit230183]

Hi,

I need to find the way to keep same value for certain date range. For example, based on image below,

I have dates and total where dates are starting from FRIDAY (1/16/2026), now until next friday totals are different which is wrong. I want to keep same number as Friday has until next Friday. So from 1/16 till 1/22 it should be 2900 and 1/23 till 1/29 it should be 300 only.

Any guidance or suggestion how to achieve this?

Thank you in Advance for any help.

Thanks

[jgeddes · Accepted solution]

I would do this in Power Query.
Use the Table.ReplaceValue function to set any non-Friday date total to null and then use the Table.FillDown function to fill in the nulls with the previous Friday value. Of course this assumes the table is sorted. 
Here is a sample code you can play with.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc9BCsMwDETRu2gdiDSOJfksJve/RounjVu0MzyE/8wpJofYaX5C4e83hqrcxxQQ4oHMvqARckPw4iKMB8IJfQF0X3SCE+znj1wQBGxovEhC+0LTT+4gXCXXlNJLr3E7vAQbxyNKsXE9siQb52P8N98v", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [NO = _t, Date = _t, Total = _t]),
    intial_type_set = Table.TransformColumnTypes(Source,{{"NO", Int64.Type}, {"Date", type date}}),
    replace_non_fridays = Table.ReplaceValue(intial_type_set,each [Total],each if Date.DayOfWeekName([Date]) = "Friday" then [Total] else null,Replacer.ReplaceValue,{"Total"}),
    fill_down = Table.FillDown(replace_non_fridays,{"Total"}),
    set_total_type = Table.TransformColumnTypes(fill_down,{{"Total", Int64.Type}})
in
    set_total_type

[cengizhanarslan · Accepted solution]

Please try the formula below:

Friday Total =
VAR _CurrentDate =
    MAX ( DimDate[Date] )

VAR _LastFriday =
    CALCULATE (
        MAX ( DimDate[Date] ),
        FILTER (
            ALL ( DimDate ),
            DimDate[Date] <= _CurrentDate
                && WEEKDAY ( DimDate[Date], 2 ) = 5
        )
    )
RETURN
CALCULATE (
    SUM ( Fact[Total] ),
    DimDate[Date] = _LastFriday
)

[damit230183]

Thank you all for your responses. Luckily, all solutions worked as expected thanks a lot.

However, good question on visuals based on this calculation. 

If I want to keep same weekly(Friday total) in WEEKLY chart then how would i do?

basically currently I am getting 21000 on x axis instead of just 3000 which friday number.

I tried dont summarize on column but didnt work out.

THanks

[ryan_mayu · Accepted solution]

@damit230183 

you can try this to create a column

Column =

var _date=maxx(FILTER('Table','Table'[date]<=EARLIER('Table'[date])&&weekday('Table'[date],1)=6),'Table'[date])

return maxx(FILTER('Table','Table'[date]=_date),'Table'[Total])

pls see the attachment below

[cengizhanarslan · Accepted solution]

Please try the formula below:

Friday Total =
VAR _CurrentDate =
    MAX ( DimDate[Date] )

VAR _LastFriday =
    CALCULATE (
        MAX ( DimDate[Date] ),
        FILTER (
            ALL ( DimDate ),
            DimDate[Date] <= _CurrentDate
                && WEEKDAY ( DimDate[Date], 2 ) = 5
        )
    )
RETURN
CALCULATE (
    SUM ( Fact[Total] ),
    DimDate[Date] = _LastFriday
)

[jgeddes · Accepted solution]

I would do this in Power Query.
Use the Table.ReplaceValue function to set any non-Friday date total to null and then use the Table.FillDown function to fill in the nulls with the previous Friday value. Of course this assumes the table is sorted. 
Here is a sample code you can play with.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc9BCsMwDETRu2gdiDSOJfksJve/RounjVu0MzyE/8wpJofYaX5C4e83hqrcxxQQ4oHMvqARckPw4iKMB8IJfQF0X3SCE+znj1wQBGxovEhC+0LTT+4gXCXXlNJLr3E7vAQbxyNKsXE9siQb52P8N98v", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [NO = _t, Date = _t, Total = _t]),
    intial_type_set = Table.TransformColumnTypes(Source,{{"NO", Int64.Type}, {"Date", type date}}),
    replace_non_fridays = Table.ReplaceValue(intial_type_set,each [Total],each if Date.DayOfWeekName([Date]) = "Friday" then [Total] else null,Replacer.ReplaceValue,{"Total"}),
    fill_down = Table.FillDown(replace_non_fridays,{"Total"}),
    set_total_type = Table.TransformColumnTypes(fill_down,{{"Total", Int64.Type}})
in
    set_total_type

[damit230183]

Thank you for responding back.

If possible can please share pbix file here.

[jgeddes]

Here you go.

[damit230183]

Thank you I will try this solution and let you know whether it works or not.

Thank You