Week ending calculations and vizualisation

IliasKatsis , Try using below DAX

 

Offer Display =
VAR ActivePromoDates =
FILTER (
'Promo raw data',
'Promo raw data'[Start Date] <= MAX ( 'ISO Weeks'[Date] )
&& 'Promo raw data'[End Date] >= MIN ( 'ISO Weeks'[Date] )
)
VAR DistinctOffers =
VALUES ( 'Promo raw data'[Offer ID] )
VAR OfferConcatenation =
CONCATENATEX (
DistinctOffers,
VAR CurrentOfferID = 'Promo raw data'[Offer ID]
VAR OfferText =
CALCULATE (
MAX ( 'Event Details'[Offer Human] ),
'Event Details'[Offer ID] = CurrentOfferID
)
RETURN
OfferText & " ("
& FORMAT (
CALCULATE (
MIN ( 'Promo raw data'[Start Date] ),
'Promo raw data'[Offer ID] = CurrentOfferID
),
"DD/MM"
) & "-"
& FORMAT (
CALCULATE (
MAX ( 'Promo raw data'[End Date] ),
'Promo raw data'[Offer ID] = CurrentOfferID
),
"DD/MM"
) & " "
& CALCULATE (
MAX ( 'Promo raw data'[Media type] ),
'Promo raw data'[Offer ID] = CurrentOfferID
) & ")",
", "
)
RETURN
OfferConcatenation

Hi bhanu_gautam ,thanks for the quick reply, I'll add more.

Hi IliasKatsis ,

I assumed some data.

Use Power Query to calculate the weeks of the year to which 'Begin' and 'End' belong.

Create an additional column to calculate the total number of weeks from start to end, separated by commas.

let
    Source = Excel.Workbook(File.Contents("yoursource"), null, true),
    Table_Sheet = Source{[Item="Table",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Table_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Begin", type date}, {"End", type date}, {"Order ID", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "BeginWeekNumber", each Date.WeekOfYear([Begin])),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "EndWeekNumber", each Date.WeekOfYear([End])),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom", each Text.Combine(List.Transform(List.Numbers([BeginWeekNumber], [EndWeekNumber] - [BeginWeekNumber] + 1), each Text.From(_)), ",")),

Then split by comma

Create a connection with your date table

Final output

 

Best Regards,
Wenbin Zhou