Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Get Fabric Certified for FREE during Fabric Data Days. Don't miss your chance! Request now

Reply
bartek_pepper
Frequent Visitor

Sophisticated Dashboard requirements - need help

8 hours ago

Hello Team!

I need to prepare the report based on the following requirements:

There is a matrix that shows names as rows, YearMonth as columns, Sum of Value as Values.

  • Based on YearMonth slicer, select only YearMonths backwards from the selected one. Ex. If I choose March, I would like to see January-March, if October, January-October.
  • Select top 15 names based on the value from the selected month. Ex. I choose March, I get 15 top names based on the sum value for March – still have January-March data as in the above requirement.
  • Modify colors of cells based on values from months, green color when value increases month-to-month and red one when values decrease ( in the static measure there a gray color as “else” but it is not relevant now).

The following screen shows solution by static measures.

bartek_pepper_0-1764859694286.png

 

I am attaching measure example and color measure example:

Value February 2025 =

CALCULATE(

SUM(Final_table[value]),

'Date table'[YearMonth] = "2025-02")

 

 

Feb 2025 Color =

VAR Current_Period = [Value February 2025]

VAR Previous_Period = [Value January 2025]

RETURN

    SWITCH(

        TRUE(),

        ISBLANK(Current_Period) || ISBLANK(Previous_Period), BLANK(),

        Current_Period > Previous_Period, "Light Green",

        Current_Period < Previous_Period, "Red",

        "Gray"

    )

I put a filter for name, top 15 based on last available month value (October 2025).

 

Please let me know in case of any further queries. I am attaching PowerQuery code + excel sample data.

Thanks!

 

Date_table
let
Source = #date(2024, 1, 1), 
#"add dates until today" = List.Dates(Source, Number.From(DateTime.LocalNow())+0- Number.From(Source), #duration(1,0,0,0)), 
#"Converted to Table" = Table.FromList(#"add dates until today", Splitter.SplitByNothing(), null, null, ExtraValues.Error), 
#"Renamed Column Date" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}), 
#"Changed Type of Column Date" = Table.TransformColumnTypes(#"Renamed Column Date",{{"Date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type of Column Date", "Year", each Date.Year([Date])),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Start of Year", each Date.StartOfYear([Date])),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "End of Year", each Date.EndOfYear([Date])),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Month", each Date.Month([Date])),
    #"Added Custom4" = Table.AddColumn(#"Added Custom3", "Start of Month", each Date.StartOfMonth([Date])),
    #"Added Custom5" = Table.AddColumn(#"Added Custom4", "End of Month", each Date.EndOfMonth([Date])),
    #"Added Custom6" = Table.AddColumn(#"Added Custom5", "Days in Month", each Date.DaysInMonth([Date])),
    #"Added Custom7" = Table.AddColumn(#"Added Custom6", "Day", each Date.Day([Date])),
    #"Added Custom8" = Table.AddColumn(#"Added Custom7", "Day Name", each Date.DayOfWeekName([Date])),
    #"Added Custom9" = Table.AddColumn(#"Added Custom8", "Day of Week", each Date.DayOfWeek([Date])),
    #"Added Custom10" = Table.AddColumn(#"Added Custom9", "Day of Year", each Date.DayOfYear([Date])),
    #"Added Custom11" = Table.AddColumn(#"Added Custom10", "Month Name", each Date.MonthName([Date])),
    #"Added Custom12" = Table.AddColumn(#"Added Custom11", "Quarter", each Date.QuarterOfYear([Date])),
    #"Added Custom13" = Table.AddColumn(#"Added Custom12", "Start of Quarter", each Date.StartOfQuarter([Date])),
    #"Added Custom14" = Table.AddColumn(#"Added Custom13", "End of Quarter", each Date.EndOfQuarter([Date])),
    #"Added Custom15" = Table.AddColumn(#"Added Custom14", "Week of Year", each Date.WeekOfYear([Date])),
    #"Added Custom16" = Table.AddColumn(#"Added Custom15", "Week of Month", each Date.WeekOfMonth([Date])),
    #"Added Custom17" = Table.AddColumn(#"Added Custom16", "Start of week", each Date.StartOfWeek([Date])),
    #"Added Custom18" = Table.AddColumn(#"Added Custom17", "End of Week", each Date.EndOfWeek([Date])),
    #"Added Custom19" = Table.AddColumn(#"Added Custom18", "Fiscal Year", each if Date.Month([Date]) <= 3 then Date.Year([Date]) else 
Date.Year([Date]) + 1),
    #"Added Custom20" = Table.AddColumn(#"Added Custom19", "Fiscal Month", each if Date.Month([Date]) <= 3 then Date.Month([Date]) + 9 else Date.Month([Date]) - 3),
    #"Added Custom21" = Table.AddColumn(#"Added Custom20", "Fiscal Quarter", each Number.RoundUp([#"Fiscal Month"]/3)),
    #"Added Custom22" = Table.AddColumn(#"Added Custom21", "Year Offset", each [Year] - Date.Year(DateTime.LocalNow())),
    #"Added Custom23" = Table.AddColumn(#"Added Custom22", "Month Offset", each (([Year]-Date.Year(DateTime.LocalNow()))*12)
+([Month]-Date.Month(DateTime.LocalNow()))),
    #"Added Custom25" = Table.AddColumn(#"Added Custom23", "Day Offset ", each Duration.Days([Date] - Date.From(DateTime.LocalNow()))),
    #"Added Custom26" = Table.AddColumn(#"Added Custom25", "Quarter Offset", each ([Year]-Date.Year(DateTime.LocalNow()))*4
+([Quarter]-Date.QuarterOfYear(DateTime.LocalNow()))),
    #"Added Custom24" = Table.AddColumn(#"Added Custom26", "YearMonth", each Text.From(Date.Year([Date])) & "-" & Text.PadStart(Text.From(Date.Month([Date])), 2, "0")),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom24", each true)
in
    #"Filtered Rows"
 
Fact_table
let
    Source = Excel.Workbook(File.Contents("C:\YOUR-DIRECTORY-test_data.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Name", type text}, {"Value", type number}}),
 
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Renamed Columns" = Table.RenameColumns(#"Added Index",{{"Index", "ID"}}),
    #"Merged Queries" = Table.NestedJoin(#"Renamed Columns", {"Name"}, dim_name_table, {"Name"}, "dim_name_table", JoinKind.RightOuter),
    #"Expanded dim_name_table" = Table.ExpandTableColumn(#"Merged Queries", "dim_name_table", {"ID"}, {"dim_name_table.ID"})
in
    #"Expanded dim_name_table"
 
dim_name_table
 
let
    Source = Excel.Workbook(File.Contents("C:\YOUR-DIRECTORY-test_data.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Name", type text}, {"Value", type number}, {"YearMonth", type date}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Name"}),
 
    #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns"),
    #"Added Index" = Table.AddIndexColumn(#"Removed Duplicates", "Index", 1, 1, Int64.Type),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Index",{"Index", "Name"}),
    #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Index", "ID"}})
in
    #"Renamed Columns"

 

NameValueYearMonth
Alpha0,05792025-01
Bravo0,03842025-02
Charlie0,15672025-03
Delta0,12112025-04
Echo0,19482025-05
Foxtrot0,14942025-06
Golf-0,00782025-07
Hotel-0,00482025-08
India0,01332025-09
Juliet0,1032025-10
Kilo-0,00052025-11
Lima0,10582025-12
Mike0,1432025-01
November0,05272025-02
Oscar0,1442025-03
Papa0,12452025-04
Quebec0,03632025-05
Romeo0,01672025-06
Sierra0,05882025-07
Tango0,07712025-08
Uniform0,0192025-09
Victor0,11412025-10
Whiskey0,06782025-11
Xray0,11512025-12
Yankee0,15522025-01
Zulu0,16782025-02
Orion0,09512025-03
Pegasus0,18122025-04
Phoenix0,09362025-05
Atlas0,16892025-06
Alpha0,16252025-07
Bravo0,03512025-08
Charlie0,03062025-09
Delta0,01832025-10
Echo0,15822025-11
Foxtrot0,15152025-12
Golf0,03472025-01
Hotel0,14962025-02
India0,14552025-03
Juliet0,17122025-04
Kilo0,08552025-05
Lima0,1262025-06
Mike0,0592025-07
November0,11442025-08
Oscar0,0532025-09
Papa0,112025-10
Quebec0,03872025-11
Romeo0,15592025-12
Sierra0,10672025-01
Tango0,07322025-02
Uniform0,01712025-03
Victor0,17672025-04
Whiskey0,14112025-05
Xray0,09312025-06
Yankee-0,00442025-07
Zulu0,15062025-08
Orion0,10762025-09
Pegasus0,15222025-10
Phoenix0,03182025-11
Atlas0,18522025-12
Alpha0,15472025-01
Bravo0,10092025-02
Charlie0,0342025-03
Delta0,13352025-04
Echo0,17942025-05
Foxtrot0,11582025-06
Golf-0,00492025-07
Hotel0,07132025-08
India0,09942025-09
Juliet0,17562025-10
Kilo0,02832025-11
Lima0,0932025-12
Mike0,19392025-01
November0,1022025-02
Oscar0,04762025-03
Papa0,16352025-04
Quebec0,01232025-05
Romeo0,07762025-06
Sierra0,11492025-07
Tango0,06512025-08
Uniform0,16652025-09
Victor0,17132025-10
Whiskey0,18472025-11
Xray0,11952025-12
Yankee0,09752025-01
Zulu0,0842025-02
Orion0,12842025-03
Pegasus0,03022025-04
Phoenix0,18292025-05
Atlas0,03952025-06
Alpha0,1522025-07
Bravo0,09532025-08
Charlie0,12392025-09
Delta0,05622025-10
Echo0,1332025-11
Foxtrot0,05742025-12
Golf0,12822025-01
Hotel0,00292025-02
India0,05672025-03
Juliet0,05292025-04
Kilo0,14692025-05
Lima0,19022025-06
Mike0,15052025-07
November0,10952025-08
Oscar0,07952025-09
Papa0,18872025-10
Quebec0,13972025-11
Romeo0,00452025-12
Sierra0,17332025-01
Tango0,03592025-02
Uniform0,05252025-03
Victor0,10442025-04
Whiskey0,18822025-05
Xray0,06792025-06
Yankee0,12972025-07
Zulu0,10942025-08
Orion0,17682025-09
Pegasus0,01282025-10
Phoenix0,16222025-11
Atlas0,10392025-12
Alpha0,1392025-01
Bravo0,00562025-02
Charlie0,06512025-03
Delta0,03052025-04
Echo0,10012025-05
Foxtrot0,04982025-06
Golf0,16652025-07
Hotel0,0672025-08
India0,17242025-09
Juliet0,01672025-10
Kilo0,11862025-11
Lima0,01752025-12
Mike0,12522025-01
November0,18282025-02
Oscar0,00282025-03
Papa0,05662025-04
Quebec0,08082025-05
Romeo0,08632025-06
Sierra-0,00072025-07
Tango0,09592025-08
Uniform0,09162025-09
Victor0,05162025-10
Whiskey0,03062025-11
Xray0,14592025-12
Yankee0,13712025-01
Zulu0,13152025-02
Orion0,05752025-03
Pegasus0,13592025-04
Phoenix0,04562025-05
Atlas0,15332025-06
Message 1 of 3
58 Views
0
2 REPLIES 2
FBergamaschi
Solution Sage
Solution Sage

7 hours ago

Hi @bartek_pepper,

here my insights for each request:

 

There is a matrix that shows names as rows, YearMonth as columns, Sum of Value as Values.

  • Based on YearMonth slicer, select only YearMonths backwards from the selected one. Ex. If I choose March, I would like to see January-March, if October, January-October.

FB You need a disconnected table showing the year-months:
Modeling -> New Table

Selection=

ALL ( 'Date[Year-Month] )

 

Then the measure will have the code

Calculation=
VAR SelectedMonth = SELECTEDVALUE ( Selection[Year-Month] )

RETURN
SUMX ( 
     VALUES ( 'Date[Year-Month] ),
     IF ( 
      'Date[Year-Month] <= SelectedMonth,

      SUM ( Table[Value] )

)
                

 

 

supposing 'Date' is your dates table and Table[Value] is the column to aggregate

 

  • Select top 15 names based on the value from the selected month. Ex. I choose March, I get 15 top names based on the sum value for March – still have January-March data as in the above requirement.

FB where and how do you want to see this list of Customers? In a string on a Card, or ?
CONCATENATEX

TOPN ( 
    15,
    SUMMARIZE ( VALUES ( Customer), Customer[Customer key ), Customer[CustomerName] ),
    SUM ( Table[Value] )
),
Customer[Customer key] & " " & Customer[CustomerName],
" / "
) 

  • Modify colors of cells based on values from months, green color when value increases month-to-month and red one when values decrease ( in the static measure there a gray color as “else” but it is not relevant now).

    FB I suggest Conditional Formatting based on this measure

         Delta MoM = 
         [Measure] - CALCULATE ( SUM ( Table[Value] ), DATEADD ( 'Date'[Date], -1, MONTH ) )

 

The ideal thing would be to have a Measure, say Calculation = SUM ( Table[Value] ) to avoid spreading the same code everywhere so you can reference to it in the above code instaed of refereing to SUM ( Table[Value] )

 

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

Sincerely,

 

Message 3 of 3
29 Views
0
amitchandak
Super User
Super User

8 hours ago

@bartek_pepper , if you need Date range more than selected, you need a disconnected Date table either in the  slicer or the Axis(Column of the Matrix) 

You need to measure like below 

CALCULATE([net New], KEEPFILTERS(TOPN(5, ALLSELECTED('Item'[Brand]),[Net], DESC)))

Here one net is dependent on slicer value and another net on axis value. The measure will be decided based on the approach we take 

 

I have discussed the approach here 

https://youtu.be/7dPrPk6LPYU 

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
Message 2 of 3
45 Views
0

Helpful resources

Announcements
November Power BI Update Carousel

Power BI Monthly Update - November 2025

Check out the November 2025 Power BI update to learn about new features.

Fabric Data Days Carousel

Fabric Data Days

Advance your Data & AI career with 50 days of live learning, contests, hands-on challenges, study groups & certifications and more!

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.

View All