How to return column value in Power BI matrix on the basis of multiple dates (9 dates)

[gillnksandhuk]

Hi All,

I am new to power BI and struggling to display column value in Power BI Matrix Visual.

Basically , the requirement is to

1)display the duration of events as per the event start date and event end date

2) Show the name of Spondor as well on the day he sponserd. for example  on 15-03-2024 Harry sponsored a Music Contest. So Harry should also display when hierarchy drill down till day.

3) Also , different colour should be display on the basis of funding start date and end date . Like from when the particular event has funded.

I doubt m we can achieven this in a single visual , but looking forward to have second thought of the experts.

Events

Event Start Date

Event end Date

Sponsor1

Sponsor2

Sponsor3

Sponsor4

Sponsor5

Sponsor6

Sponsor1date

Sponsor2date

Sponsor3date

Sponsor4date

Sponsor5date

Sponsor6date

FundstartDate

FundEnddate

Music Contest

01/01/2023

18/09/2024

Adams

Harry

Elliotsk

Ragha

Neon

Caster

15/03/2024

01/05/2024

10/08/2024

17/05/2024

15/03/2023

18/09/2024

EXP Cricket Match

16/06/2023

31/12/2024

Adams

Harry

Elliotsk

Ragha

Neon

Caster

25/12/2025

18/09/2023

20/08/2023

20/09/2024

29/06/2024

25/07/2023

31/12/2024

Exhibition cloth

15/03/2023

30/10/2024

Adams

Harry

Elliotsk

Ragha

Neon

Caster

30/09/2023

15/09/2024

01/10/2023

11/04/2023

30/10/2024

Dance Competition

31/12/2023

20/07/2024

Adams

Harry

Elliotsk

Ragha

Neon

Caster

15/04/2024

24/04/2024

25/03/2024

05/01/2024

20/07/2024

Event2

15/04/2024

15/06/2025

Adams

Harry

Elliotsk

Ragha

Neon

Caster

11/03/2025

10/03/2024

11/03/2025

31/01/2025

18/09/2025

20/05/2024

15/06/2025

Event3

18/08/2023

31/12/2025

Adams

Harry

Elliotsk

Ragha

Neon

Caster

19/10/2025

30/05/2025

01/09/2025

20/09/2023

31/12/2025

Event4

17/03/2024

19/09/2025

Adams

Harry

Elliotsk

Ragha

Neon

Caster

25/08/2024

31/07/2025

25/07/2025

03/07/2025

30/04/2024

19/09/2025

@Ashish_Mathur  @amitchandak  @lbendlin 

Regards

gillksandhu

[gillnksandhuk]

Thanks Ashish for quick response, indeed this tranformation helps, but my main issue is to display Event happenend between a specific date range.

For example :- in this case Dance competition has a date range from 31/12/2023 till 20/07/2024 so when one drill it down to Day /Date level. value should repeat from 31/12/2023 till 20/07/2024 .

Thanks again for your help 

[Ashish_Mathur]

You are welcome.  This revised M code will create 1 row for each date between the Event Start date and event end date.  See Date.1 column.

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Events", "Event Start Date", "Event end Date", "FundstartDate", "FundEnddate"}, "Attribute", "Value"),
    #"Split Column by Character Transition" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c)), {"Attribute.1", "Attribute.2"}),
    #"Replaced Value" = Table.ReplaceValue(#"Split Column by Character Transition",null,"Sponsor",Replacer.ReplaceValue,{"Attribute.2"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","Sponsor","",Replacer.ReplaceText,{"Attribute.1"}),
    #"Pivoted Column" = Table.Pivot(#"Replaced Value1", List.Distinct(#"Replaced Value1"[Attribute.2]), "Attribute.2", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Attribute.1"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns", "Date.1", each {Number.From([Event Start Date])..Number.From([Event end Date])}),
    #"Expanded Date.1" = Table.ExpandListColumn(#"Added Custom", "Date.1"),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Date.1",{{"Events", type text}, {"Event Start Date", type date}, {"Event end Date", type date}, {"FundstartDate", type date}, {"FundEnddate", type date}, {"Sponsor", type text}, {"date", type date}, {"Date.1", type date}})
in
    #"Changed Type"

Hope this helps.

[Ashish_Mathur]

Hi,

Run this M code to transform your data into a usable table

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Events", "Event Start Date", "Event end Date", "FundstartDate", "FundEnddate"}, "Attribute", "Value"),
    #"Split Column by Character Transition" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c)), {"Attribute.1", "Attribute.2"}),
    #"Replaced Value" = Table.ReplaceValue(#"Split Column by Character Transition",null,"Sponsor",Replacer.ReplaceValue,{"Attribute.2"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","Sponsor","",Replacer.ReplaceText,{"Attribute.1"}),
    #"Pivoted Column" = Table.Pivot(#"Replaced Value1", List.Distinct(#"Replaced Value1"[Attribute.2]), "Attribute.2", "Value"),
    #"Changed Type" = Table.TransformColumnTypes(#"Pivoted Column",{{"Events", type text}, {"Event Start Date", type date}, {"Event end Date", type date}, {"FundstartDate", type date}, {"FundEnddate", type date}, {"Attribute.1", Int64.Type}, {"Sponsor", type text}, {"date", type date}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Attribute.1"})
in
    #"Removed Columns"

Hope this helps.

[gillnksandhuk]

Thanks Much Ashish for saving my life 🙂 , But the data i shared is a sample data. When i was trying to implement it in real data so it will create multiple rows (for example if an event occurred for 2 years then it will create 700+ rows)  and i have 14+ columns as well. so  i discussed the issue and now everyone agress to show the data till either Half Yearly or Quarterly . 

Any help would be appreciable.

Many thanks again 

[Ashish_Mathur]

You are welcome.  I do not know how to create one row for each month which falls between the start and end date but if you wish to create one row for each month, then refer to this article - Generating Rows by Month for Date Ranges in Power Query | by Daniel Marsh-Patrick | Daniel Marsh-Pat....

Hope this helps.

[gillnksandhuk]

THanks again Ashish , for your help . I will have a look into the article and get back to you.

😊👍