Quick Measures Gallery topics

Line Segment and Legend Color Based on Measures

amitchandak — Mon, 31 Aug 2026 08:31:55 GMT

Conditional formatting in Power BI just got a solid upgrade.

In the July 2026 update, conditional formatting now works on Line Charts and visuals with Legends. This was one of the most requested features. It lets you apply data-driven colors across multiple visual types and keep colors consistent throughout your reports.

Up until recently, when I used to interview Power BI professionals with a lot of experience in visualization, I had a good way to test them.

I'd ask one of these questions:

Which visuals don't support conditional formatting?
I made some changes to my visual and now the conditional formatting option isn't showing. Why?
How do you apply conditional formatting on Line and Pie visuals?

It was an easy way to catch gaps. Conditional formatting wasn't supported on legends. For Pie visuals, the workaround was to apply it on a Bar chart first and then switch to Pie. For Line visuals, we used to get extra color markers the same way.

Power BI has been improving its visualization experience steadily over the last two years, with big updates coming regularly. This long-awaited feature finally made it into the July 2026 update. Conditional formatting is now supported on Line visuals and Legends.

Why it was needed on Line visuals

Just like any other visual, you sometimes need line segments to change color based on a condition. This helps explain things like margins going down or current year vs previous year comparisons.

Why it was needed on Legends

Say your company always uses one color and your competitor uses another. On a report showing market share, you'd always want your company represented by the same color. That was possible for axis values but not for legends. This update fixes that. You can now apply conditional formatting on Pie, Stacked, and any other visual that uses a legend.

Please find the file where I have used different measures to do conditional formatting on Line and Legends

Measure used

Brand Color = SWITCH ( TRUE (), Max('Item'[Brand])= "Brand 1", "Yellow", Max('Item'[Brand])= "Brand 2", "Green", Max('Item'[Brand])= "Brand 3", "Blue", Max('Item'[Brand])= "Brand 4", "Red", Max('Item'[Brand])= "Brand 5", "Orange", Max('Item'[Brand])= "Brand 6", "Purple", Max('Item'[Brand])= "Brand 7", "Pink", Max('Item'[Brand])= "Brand 8", "Cyan", Max('Item'[Brand])= "Brand 9", "Lime", Max('Item'[Brand])= "Brand 10", "Brown", Max('Item'[Brand])= "Brand 11", "Gray", Max('Item'[Brand])= "Brand 12", "Teal", Max('Item'[Brand])= "Brand 13", "Magenta", "Other" )

Category Measure

Category Color = SWITCH ( TRUE (), Max('Item'[Category] )= "Category 1", "Red", Max('Item'[Category] )= "Category 2", "Green", Max('Item'[Category] )= "Category 3", "Blue", Max('Item'[Category] )= "Category 4", "Yellow", Max('Item'[Category] )= "Category 5", "Orange", "Black" )

Color Year

Max Year = max('Date'[Year])

You can also check the video on the same -

https://www.youtube.com/watch?v=SDX1gUpcJaw&list=PLPaNVDMhUXGYo50Ajmr4SgSV9HIQLxc8L&index=1

Data Analysis Expressions - Customized Martix Table

DallasBaba — Thu, 11 Dec 2025 03:51:33 GMT

This Power BI report display historical trends from 2018–2023 of the Ozone Season Heat Input (MMBtu) across U.S. states and fuel types. It highlight the developement of a customized matrix visual for displaying the Yearly record along with the CAGR %, shared %, and YOY % on a Martix table with a dynamic state slicer for environmental compliance and emissions analysis across states.

This is a continuous project :

1st : Adjusting-Unadjusted-Emissions-with-AI-Solution

2nd : Updated view - 2022 Unadjusted NOx Emissions

_M KPI_Selector_Value = VAR _id = SELECTEDVALUE ( KPI_MatrixAxis[ID] ) VAR _display = SELECTEDVALUE ( KPI_MatrixAxis[Display] ) VAR _year = IF ( _id = 1, VALUE ( _display ) ) VAR YearlyTotal = CALCULATE ( [Total HTIOZ], REMOVEFILTERS ( 'Unadjusted Annual Unit'[Data Year] ), KEEPFILTERS ( 'Unadjusted Annual Unit'[Data Year] = _year ) ) VAR YoY = [_YOY % HTIOZD Data] VAR CAGR = [3Y CAGR HTIOZ] VAR Share_of_Total = DIVIDE ( [Total HTIOZ], CALCULATE ( [Total HTIOZ], ALLSELECTED ( 'Unadjusted Annual Unit' ) ) ) RETURN SWITCH ( TRUE(), _id = 1, FORMAT(YearlyTotal,"#,##"), _id = 2, YoY , --FORMAT(YoY,"0.0%"), _id = 3, CAGR, -- FORMAT(CAGR,"0.0%"), _id = 4, FORMAT(Share_of_Total,"0.0%"), BLANK() )

eyJrIjoiYTMzYWI3NWYtM2RjZC00MzRhLWIzNmUtYWQ0MWY2Mjc4MWEzIiwidCI6IjNlMjFhMTFlLTc3MDctNDdmOC1iMzRhLTc5YTQ2YTQ0ZTk5MyIsImMiOjF9

SVG Rich Label Generator

wujunmin — Fri, 05 Dec 2025 01:43:37 GMT

With the generator above, you can add rounded backgrounds and icons to labels.

eyJrIjoiMzU1NjFkNmMtNzU1ZS00M2EzLWE3OTQtNjYyMDI3NjY1ZDUzIiwidCI6IjI5Y2JkNTY4LTBlOWItNDQ0Zi1iZTA1LTYxNjMyOTAzNjJmZSJ9

DIY SVG Progress Bar

wujunmin — Thu, 04 Dec 2025 06:21:28 GMT

DIY Tool:

https://app.powerbi.com/view?r=eyJrIjoiNjdmMWM0ODAtMTgyZC00Y2ZkLWIxZTctMjI4YjNlNzk4OTU4IiwidCI6IjI5Y2JkNTY4LTBlOWItNDQ0Zi1iZTA1LTYxNjMyOTAzNjJmZSJ9

eyJrIjoiNjdmMWM0ODAtMTgyZC00Y2ZkLWIxZTctMjI4YjNlNzk4OTU4IiwidCI6IjI5Y2JkNTY4LTBlOWItNDQ0Zi1iZTA1LTYxNjMyOTAzNjJmZSJ9

DIY SVG Icon Bar For Table or Matrix

wujunmin — Thu, 04 Dec 2025 04:28:31 GMT

How to use?

link https://app.powerbi.com/view?r=eyJrIjoiZDE4NmYwMTQtMzFiZi00MGM0LTgyODAtODk3MTkxZTBiODU2IiwidCI6IjI5Y2JkNTY4LTBlOWItNDQ0Zi1iZTA1LTYxNjMyOTAzNjJmZSJ9

eyJrIjoiZDE4NmYwMTQtMzFiZi00MGM0LTgyODAtODk3MTkxZTBiODU2IiwidCI6IjI5Y2JkNTY4LTBlOWItNDQ0Zi1iZTA1LTYxNjMyOTAzNjJmZSJ9

DIY SVG Waffle Charts

wujunmin — Thu, 04 Dec 2025 04:16:53 GMT

Search & Choose an icon, then copy the SVG Measure below.

https://app.powerbi.com/view?r=eyJrIjoiM2EwYjEwNDAtZTAyNS00NGFmLWE3YmUtNWYwNTBmMWFhNTY3IiwidCI6IjI5Y2JkNTY4LTBlOWItNDQ0Zi1iZTA1LTYxNjMyOTAzNjJmZSJ9

eyJrIjoiM2EwYjEwNDAtZTAyNS00NGFmLWE3YmUtNWYwNTBmMWFhNTY3IiwidCI6IjI5Y2JkNTY4LTBlOWItNDQ0Zi1iZTA1LTYxNjMyOTAzNjJmZSJ9

SVG Bubble Charts

wujunmin — Thu, 04 Dec 2025 03:48:46 GMT

Bubble charts are a versatile type of visualization that can illustrate any metric involving size comparison. The attached SVG bubble chart measures can be used in table, matrix, and slicer.

Ticket Backlog (How many were open on specific dates)

wardy912 — Tue, 12 Aug 2025 14:50:27 GMT

Incident Backlog =

VAR CurrentDate = MAX('Date'[Date])

VAR ActiveTickets =

CALCULATE(DISTINCTCOUNT(Incidents[Number]),

ALL('Date'),

'Date'[Date]<=CurrentDate,

ISBLANK(Incidents[Resolved Date])

||Incidents[Resolved Date]>=CurrentDate)

Return

ActiveTickets

Date table with Calendar and Fiscal year

wardy912 — Mon, 11 Aug 2025 11:48:32 GMT

DateFiscal = ADDCOLUMNS ( CALENDAR ( MIN ( 'Table'[Date column] ), MAX ( 'Table'[Date column] ) ), "MonthNo", MONTH ( [Date] ), "MonthName", FORMAT ( [Date], "MMMM" ), "MonthYear", FORMAT ( [Date], "MMMM YYYY" ), "MonthYearShort", FORMAT ( [Date], "MMM YY" ), "MonthYearNo", FORMAT ( [Date], "YYYYMM" ), "Quarter", QUARTER ( [Date] ), "Year", YEAR ( [Date] ), "QuarterYear", "Q" & FORMAT ( QUARTER ( [Date] ), "0" ) & " " & FORMAT ( YEAR ( [Date] ), "0000" ), "YearQuarterNo", FORMAT ( YEAR ( [Date] ), "0000" ) & FORMAT ( QUARTER ( [Date] ), "00" ), "Day", DAY ( [Date] ), "WeekNumber", WEEKNUM ( [Date] ), "WeekdayNum", WEEKDAY ( [Date] ), "WeekdayName", FORMAT ( [Date], "DDDD" ), "PreviousWeek", WEEKNUM ( [Date] ) -1 , "WeekStartDate", ([Date] - WEEKDAY ( [Date] , 1 ) +1), "WeekEndDate", ([Date] - WEEKDAY ( [Date] , 1 ) +7), "YearMonth", FORMAT ( [Date], "YYYY-M" ), "FiscalQuarter", SWITCH ( TRUE(), MONTH ( [Date] ) IN {8, 9, 10}, "Q1", MONTH ( [Date] ) IN {11, 12, 1}, "Q2", MONTH ( [Date] ) IN {2, 3, 4}, "Q3", MONTH ( [Date] ) IN {5, 6, 7}, "Q4" ), "FiscalYear", YEAR ( DATE ( YEAR ( [Date] ) - IF ( MONTH ( [Date] ) < 8, 1, 0 ), 8, 1 ) ), "FiscalQuarterNo", FORMAT ( YEAR ( DATE ( YEAR ( [Date] ) - IF ( MONTH ( [Date] ) < 8, 1, 0 ), 8, 1 ) ), "0000" ) & SWITCH ( TRUE(), MONTH ( [Date] ) IN {8, 9, 10}, "01", MONTH ( [Date] ) IN {11, 12, 1}, "02", MONTH ( [Date] ) IN {2, 3, 4}, "03", MONTH ( [Date] ) IN {5, 6, 7}, "04" ), "FiscalQuarterYear", SWITCH ( TRUE(), MONTH ( [Date] ) IN {8, 9, 10}, "Q1", MONTH ( [Date] ) IN {11, 12, 1}, "Q2", MONTH ( [Date] ) IN {2, 3, 4}, "Q3", MONTH ( [Date] ) IN {5, 6, 7}, "Q4" ) & " " & FORMAT ( YEAR ( DATE ( YEAR ( [Date] ) - IF ( MONTH ( [Date] ) < 8, 1, 0 ), 8, 1 ) ), "0000" ) )

Map Lines

Greg_Deckler — Fri, 18 Jul 2025 17:48:55 GMT

Came out of this conversation: Create Origin To Destination Map In BI Desktop - Microsoft Fabric Community. Here's a way to create origin to destination lines on a map given a table of cities.

CityLatitudeLongitude

Columbus, OH	39.9611111	-82.9988889
London, UK	51.5	-0.116667
Kansas City	39.099912	-94.581213
St. Louis	38.627089	-90.200203

From and To = VAR __NumSteps = 100 VAR __Steps = SELECTCOLUMNS( GENERATESERIES( 1, __NumSteps, 1 ), "Step", [Value] ) VAR __BaseTable = FILTER( GENERATE( SELECTCOLUMNS( 'Cities', "From City", [City], "From Long", [Longitude], "From Lat", [Latitude] ), SELECTCOLUMNS( 'Cities', "To City", [City], "To Long", [Longitude], "To Lat", [Latitude] ) ), [From City] <> [To City] ) VAR __String = CONCATENATEX( __BaseTable, [From City] & "^" & [From Long] & "^" & [From Lat] & "^" & [To City] & "^" & [To Long] & "^" & [To Lat], "|" ) VAR __Count = COUNTROWS( __BaseTable ) VAR __Table = ADDCOLUMNS( GENERATESERIES( 1, __Count ), "__Data", SUBSTITUTE( PATHITEM( __String, [Value] ), "^", "|" ) ) VAR __Result = FILTER( SELECTCOLUMNS( ADDCOLUMNS( GENERATE( __Table, __Steps ), "Latitude", PATHITEM( [__Data], 3 ) + ( [Step] / __NumSteps ) * ( PATHITEM( [__Data], 6 ) - PATHITEM( [__Data], 3 ) ), "Longitude", PATHITEM( [__Data], 2 ) + ( [Step] / __NumSteps) * ( PATHITEM( [__Data], 5 ) - PATHITEM( [__Data], 2 ) ) ), "Latitude", [Latitude], "Longitude", [Longitude], "From", PATHITEM( [__Data], 1 ), "To", PATHITEM( [__Data], 4 ) ), [From] <> [To] ) RETURN __Result

eyJrIjoiMDZkNjhlMmItZTg1ZC00ZmM2LTkwZTEtNDJiZDA3ZGFlNDRjIiwidCI6Ijg3NDlmOWI5LWYzMmQtNDdhMS1hMjI0LTM2OTQxOGFlMmY1MSJ9

BINOM.INV Approximation

Greg_Deckler — Sun, 20 Jul 2025 17:06:46 GMT

Been a while since I published a Quick Measure. Here is one that is an update to my previously published BINOM.INV. The problem with that version is that it relied on the FACT function which tops out at 170. So, for populations larger than 170 use this one which is an extremely accurate "approximation".

BINOM_INV_APPROX = VAR __n = [n Value] VAR __Alpha = [Alpha Value] VAR __p = [p Value] VAR __pi = PI() -- Stirling's approximation for ln(n!) defined inline VAR __LnFactorial = ADDCOLUMNS( GENERATESERIES( 0, __n ), "ln_n!", VAR __k = [Value] VAR __Result = IF( __k = 0, 0, __k * LN( __k ) - __k + 0.5 * LN( 2 * __pi * __k ) ) RETURN __Result ) -- Join ln(n!), ln(k!), ln(n-k!) for each k VAR __Table = ADDCOLUMNS( __LnFactorial, "ln_k!", MAXX( FILTER( __LnFactorial, [Value] = EARLIER( [Value] ) ), [ln_n!] ), "ln_n-k!", MAXX( FILTER( __LnFactorial, [Value] = __n - EARLIER( [Value] ) ), [ln_n!] ), "LogB", VAR __k = [Value] VAR __ln_n_fact = __n * LN(__n) - __n + 0.5 * LN(2 * __pi * __n) VAR __ln_k_fact = IF( __k = 0, 0, __k * LN( __k ) - __k + 0.5 * LN( 2 * __pi * __k ) ) VAR __ln_nk_fact = VAR __nk = __n - __k VAR __Result = IF( __nk = 0, 0, __nk * LN( __nk ) - __nk + 0.5 * LN( 2 * __pi * __nk ) ) RETURN __Result VAR __Result = __ln_n_fact - __ln_k_fact - __ln_nk_fact + __k * LN( __p ) + ( __n - __k ) * LN( 1 - __p ) RETURN __Result ) VAR __TableWithProb = ADDCOLUMNS( __Table, "B", EXP( [LogB] ) ) VAR __CumulativeTable = ADDCOLUMNS( __TableWithProb, "Cumulative", SUMX( FILTER( __TableWithProb, [Value] <= EARLIER( [Value] ) ), [B] ) ) VAR __Result = MINX( FILTER(__CumulativeTable, [Cumulative] >= __Alpha), [Value] ) RETURN __Result

eyJrIjoiMDMxMzM1ZDktZDdlYy00NWFiLWE1MWMtYzU1ZTU3ZmZkOGYzIiwidCI6Ijg3NDlmOWI5LWYzMmQtNDdhMS1hMjI0LTM2OTQxOGFlMmY1MSJ9

DAX-Based Fully Customizable SVG Gauge Visual

rnewby3 — Wed, 23 Apr 2025 14:57:23 GMT

Hey everyone,

I had a requirement to use a gauge visual in one of my reports, but the users didn't like the stock gauge visual so I built my own in DAX using SVGs. It's fully customizable using the settings section, but also feel free to take the code and tweak it however you want. Hopefully this helps others who wanted more control over their gauge.

GaugeWithTargetLabel = // ─────────── Measures ─────────── VAR ProgressMeasure = [Progress Measure] VAR TargetMeasure = [Target Measure] // ─────────── Settings ─────────── VAR KPI_LabelText = "Marketing" VAR KPI_LabelFontSize = 10 VAR KPI_LabelFontColor = "#666666" VAR Progress_Color = "#3CB371" VAR Bg_Color = "#e0e0e0" VAR KPI_FontSize = 20 VAR KPI_FontColor = "#666666" VAR Target_FontSize = 10 VAR Target_FontColor = "#1A1A1A" VAR Target_Color = "#FF0000" VAR Top_Margin = 12 VAR Bottom_Spacing = 5 VAR Bottom_Margin = KPI_FontSize // normalize 0→1 VAR ProgressPct = DIVIDE(ProgressMeasure,100,0) VAR TargetPct = DIVIDE(TargetMeasure, 100,0) // gauge geometry VAR Radius = 50 VAR StrokeWidth = 10 VAR HalfStroke = StrokeWidth/2 // center of circle (inside the inner drawing area) VAR CX = Radius + HalfStroke VAR CY = Radius + HalfStroke // size of inner area VAR InnerW = CX*2 VAR InnerH = CY + HalfStroke // full canvas size (with left/top margin + bottom margin) VAR Width = InnerW + 2*Top_Margin VAR Height = InnerH + Bottom_Spacing + KPI_FontSize + Top_Margin + Bottom_Margin // ── Progress arc endpoint ─────────────────────────────────── VAR EndAngleDeg = ProgressPct * 180 VAR EndTheta = 180 - EndAngleDeg VAR EndRad = RADIANS(EndTheta) VAR EndX = CX + Radius * COS(EndRad) VAR EndY = CY - Radius * SIN(EndRad) // ── Paths ──────────────────────────────────────────────────── VAR BgPath = "M " & (CX-Radius) & "," & CY & " A " & Radius & "," & Radius & " 0 0,1 " & (CX+Radius) & "," & CY VAR ProgPath = "M " & (CX-Radius) & "," & CY & " A " & Radius & "," & Radius & " 0 0,1 " & EndX & "," & EndY // ── Target tick endpoints ──────────────────────────────────── VAR TickAngleDeg = TargetPct * 180 VAR TickTheta = 180 - TickAngleDeg VAR TickRad = RADIANS(TickTheta) VAR InnerR = Radius - HalfStroke VAR OuterR = Radius + HalfStroke VAR TX1 = CX + InnerR * COS(TickRad) VAR TY1 = CY - InnerR * SIN(TickRad) VAR TX2 = CX + OuterR * COS(TickRad) VAR TY2 = CY - OuterR * SIN(TickRad) // ── Floating target % label coords ─────────────────────────── VAR LabelR = OuterR + Top_Margin VAR LX = CX + LabelR * COS(TickRad) VAR LY = CY - LabelR * SIN(TickRad) // ── KPI positions ──────────────────────────────────────────── VAR KPITextY = CY + HalfStroke // bottom of the arc VAR KPILabelY = KPITextY + Bottom_Spacing // top of KPI label RETURN "data&colon;image/svg+xml;utf8," & "<svg xmlns='http://www.w3.org/2000/svg' width='" & Width & "' height='" & Height & "' viewBox='0 0 " & Width & " " & Height & "'>" & // Shift everything right & down by Top_Margin "<g transform='translate(" & Top_Margin & "," & Top_Margin & ")'>" & // gray rail "<path d='" & BgPath & "' fill='none' stroke='" & Bg_Color & "' stroke-width='" & StrokeWidth & "' stroke-linecap='round'/>" & // green progress "<path d='" & ProgPath & "' fill='none' stroke='" & Progress_Color & "' stroke-width='" & StrokeWidth & "' stroke-linecap='round'/>" & // target tick "<line x1='" & TX1 & "' y1='" & TY1 & "' x2='" & TX2 & "' y2='" & TY2 & "' stroke='" & Target_Color & "' stroke-width='2'/>" & // floating target % "<text x='" & LX & "' y='" & LY & "' text-anchor='middle' font-family='Segoe UI' " & "font-size='" & Target_FontSize & "' fill='" & Target_FontColor & "' dominant-baseline='middle'>" & FORMAT(TargetPct,"0%") & "</text>" & // main KPI % "<text x='" & CX & "' y='" & KPITextY & "' text-anchor='middle' font-family='Segoe UI' " & "font-size='" & KPI_FontSize & "' fill='" & KPI_FontColor & "' dominant-baseline='text-after-edge'>" & FORMAT(ProgressPct,"0%") & "</text>" & // KPI label below "<text x='" & CX & "' y='" & KPILabelY & "' text-anchor='middle' font-family='Segoe UI' " & "font-size='" & KPI_LabelFontSize & "' fill='" & KPI_LabelFontColor & "' dominant-baseline='hanging'>" & KPI_LabelText & "</text>" & "</g>" & "</svg>"

Relatório Financeiro

AgabrielaMarian — Fri, 21 Feb 2025 14:39:03 GMT

Streaks!

Greg_Deckler — Fri, 09 Aug 2024 13:21:31 GMT

To some extent, a practical implementation of Cthulhu (Cthulhu - Microsoft Fabric Community). However, these versions identify the longest streak and current streak, use dates instead of an index and is a much cleaner/better implementation of Cthulhu.

A streak is simply something that has happened continuously, every day for a period of time.

Longest Streak Sub-Project = VAR __SubProject = MAX( 'Table'[Sub-projects] ) VAR __MinDate = MINX( FILTER( ALL( 'Table' ), [Sub-projects] = __SubProject ), [Start date] ) VAR __MaxDate = MAXX( FILTER( ALL( 'Table' ), [Sub-projects] = __SubProject ), [Start date] ) VAR __Table = ADDCOLUMNS( CALENDAR( __MinDate, __MaxDate ), "__Count", VAR __Date = [Date] VAR __Result = IF( COUNTROWS( FILTER( ALL( 'Table' ), [Start date] = __Date && [Sub-projects] = __SubProject ) ), 1, 0 ) RETURN __Result ) VAR __Table1 = ADDCOLUMNS( __Table, "Cthulhu", VAR __Index = [Date] VAR __Group = [__Count] VAR __TmpTable1 = FILTER( __Table, [__Count] = __Group && [Date] < __Index) VAR __TmpTable2 = ADDCOLUMNS( __TmpTable1, "__diff", ( [Date] - MAXX( FILTER( __Table, [Date] < EARLIER( [Date] ) && [__Count] = EARLIER( [__Count] ) ), [Date] ) ) * 1. ) VAR __Max = MAXX( __TmpTable2, [Date]) VAR __MaxStart = MAXX( FILTER(__TmpTable2, [__diff] > 1), [Date]) VAR __TmpTable3 = FILTER( __TmpTable2, [Date] >= __MaxStart) VAR __Result = SWITCH( TRUE(), ISBLANK( __Max ), 1, __Max = __index - 1, COUNTROWS( __TmpTable3 ) + 1, 1 ) RETURN __Result ) VAR __Result = MAXX( FILTER( __Table1, [__Count] <> 0 ), [Cthulhu] ) RETURN __Result

Current Streak Sub-Project = VAR __SubProject = MAX( 'Table'[Sub-projects] ) VAR __MaxDate = MAXX( ALL( 'Table' ), [Start date] ) VAR __MinDate = MINX( FILTER( ALL( 'Table' ), [Sub-projects] = __SubProject ), [Start date] ) VAR __Table = ADDCOLUMNS( CALENDAR( __MinDate, __MaxDate ), "__Count", VAR __Date = [Date] VAR __Result = IF( COUNTROWS( FILTER( ALL( 'Table' ), [Start date] = __Date && [Sub-projects] = __SubProject ) ), 1, 0 ) RETURN __Result ) VAR __Table1 = ADDCOLUMNS( __Table, "Cthulhu", VAR __Index = [Date] VAR __Group = [__Count] VAR __TmpTable1 = FILTER( __Table, [__Count] = __Group && [Date] < __Index) VAR __TmpTable2 = ADDCOLUMNS( __TmpTable1, "__diff", ( [Date] - MAXX( FILTER( __Table, [Date] < EARLIER( [Date] ) && [__Count] = EARLIER( [__Count] ) ), [Date] ) ) * 1. ) VAR __Max = MAXX( __TmpTable2, [Date]) VAR __MaxStart = MAXX( FILTER(__TmpTable2, [__diff] > 1), [Date]) VAR __TmpTable3 = FILTER( __TmpTable2, [Date] >= __MaxStart) VAR __Result = SWITCH( TRUE(), ISBLANK( __Max ), 1, __Max = __index - 1, COUNTROWS( __TmpTable3 ) + 1, 1 ) RETURN __Result ) VAR __CurrentCount = MAXX( FILTER( __Table1, [Date] = __MaxDate ), [__Count] ) VAR __Result = IF( __CurrentCount = 0, 0, MAXX( FILTER( __Table1, [Date] = __MaxDate ), [Cthulhu] ) ) RETURN __Result

eyJrIjoiZGVlMjU3ZGMtZjQ0NC00MjkwLWE3NjgtYWI1NzhjZWMzZWUwIiwidCI6Ijg3NDlmOWI5LWYzMmQtNDdhMS1hMjI0LTM2OTQxOGFlMmY1MSJ9

Various Lookup Table Designs

aduguid — Mon, 08 Jul 2024 04:59:05 GMT

Here are some various ways of creating lookup tables in Power BI.

This table is constructed with the UNION and ROW functions in DAX.

Boolean Types = UNION ( ROW ("Label", "Yes", "Order", 1) , ROW ("Label", "No", "Order", 2) )

This table is constructed with using the DATATABLE function in DAX.

Boolean Types = VAR _result = DATATABLE ( "Label" , STRING , "Order" , INTEGER , { {"Yes" , 1} , {"No" , 2} } ) RETURN _result

This table was created using Power Query in M.

let Source = Table.FromRecords({ [Label = "Yes", Order = 1], [Label = "No", Order = 2] }) in Source

This table was created using "Enter data" button from the "Home" ribbon.

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WikwtVtJRMlSK1YlW8ssHMo2UYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Label = _t, Order = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Label", type text}, {"Order", Int64.Type}}) in #"Changed Type"

Dynamic Number Format

aduguid — Mon, 08 Jul 2024 04:31:00 GMT

This can be used after selecting the "Dynamic" as the measure format under "Measure tools"

VAR CurrentValue = SELECTEDMEASURE() RETURN SWITCH ( TRUE (), // format the number based on value CurrentValue <= -1E6, "#,0,,.00 M", CurrentValue <= -1E3, "#,0,.00 K", CurrentValue <= 1E3, "#,0.00", CurrentValue < 1E6, "#,0,.00 K", CurrentValue < 1E9, "#,0,,.00 M", CurrentValue < 1E12, "#,0,,,.00 B" )

Ageing Table

aduguid — Mon, 08 Jul 2024 04:18:12 GMT

Ageing table in DAX

Calendar Ageing = VAR _today_date = TODAY() //'Properties'[Today Date] VAR _min_date1 = CALCULATE(MIN(Table1[Date1])) VAR _min_date2 = CALCULATE(MIN(Table2[Date2])) VAR _future_date = IF(_min_date1 < _min_date2, _min_date1, _min_date2) //VAR _future_date = DATE( YEAR( _today_date ) - 6, 01, 01 ) VAR _result = UNION ( ADDCOLUMNS (CALENDAR ( _today_date - 30, _today_date), "Ageing Days", "0 to 30", "Ageing Days Order", 1) , ADDCOLUMNS (CALENDAR ( _today_date - 60, _today_date - 31 ), "Ageing Days", "31 to 60", "Ageing Days Order", 2) , ADDCOLUMNS (CALENDAR ( _today_date - 90, _today_date - 61 ), "Ageing Days", "61 to 90", "Ageing Days Order", 3) , ADDCOLUMNS (CALENDAR ( _today_date - 120, _today_date - 91 ), "Ageing Days", "91 to 120", "Ageing Days Order", 4) , ADDCOLUMNS (CALENDAR ( _future_date, _today_date - 121), "Ageing Days", "120+", "Ageing Days Order", 5) ) RETURN _result

Timeframe Table

aduguid — Mon, 08 Jul 2024 04:08:10 GMT

Timeframe table in DAX

Calendar Timeframe = VAR _today_date = TODAY() //'Properties'[Today Date] VAR _yesterday_date = _today_date - 1 VAR _week_start = _today_date - WEEKDAY ( _today_date, 2 ) VAR _week_end = _today_date - WEEKDAY ( _today_date, 2 ) + 6 VAR _month_start = DATE( YEAR(_today_date), MONTH(_today_date), 01 ) VAR _month_end = EOMONTH( _today_date, 0) VAR _quarter_start = DATE ( YEAR (_today_date), ROUNDUP ( DIVIDE ( MONTH (_today_date), 3 ), 0 ) * 3 - 2, 1 ) VAR _quarter_end = EOMONTH(EDATE(_quarter_start, 2), 0) VAR _fiscal_year = YEAR(EDATE( _today_date, 6)) VAR _fiscal_year_start = DATE( _fiscal_year - 1, 07, 01) VAR _fiscal_year_end = DATE( _fiscal_year, 06, 30) VAR _tomorrow_date = IF(_today_date + 1 > _fiscal_year_end, _fiscal_year_end, _today_date + 1) VAR _calendar_year = YEAR(_today_date) VAR _calendar_year_start = DATE( _calendar_year , 01, 01) VAR _calendar_year_end = DATE( _calendar_year, 12, 31) VAR _previous_month_start = IF(MONTH(_today_date) = 1, DATE(YEAR(_today_date)-1, 12, 1), DATE(YEAR(_today_date), MONTH(_today_date)-1, 1)) VAR _previous_month_end = DATE(YEAR(_previous_month_start), MONTH(_previous_month_start), DAY(EOMONTH(_previous_month_start, 0))) VAR _previous_quarter_start = EDATE(_quarter_start, -3) VAR _previous_quarter_end = EOMONTH(EDATE(_quarter_start, -1), 0) VAR _previous_fiscal_year_start = DATE( _fiscal_year - 2, 07, 01) VAR _previous_fiscal_year_end = DATE( _fiscal_year - 1, 06, 30) VAR _previous_calendar_year_start = DATE( _calendar_year - 1, 01, 01) VAR _previous_calendar_year_end = DATE( _calendar_year - 1, 12, 31) VAR _today_date_py = DATE( YEAR(_today_date) - 1, MONTH(_today_date), DAY(_today_date) ) VAR _week_start_py = DATE( YEAR(_today_date_py), 1 , 1) + (WEEKNUM(_today_date_py) - 1 ) * 7 VAR _month_start_py = DATE( YEAR(_today_date_py), MONTH(_today_date), 01 ) VAR _quarter_start_py = DATE( YEAR(_quarter_start) - 1, MONTH(_quarter_start), 01 ) VAR _fiscal_year_start_py = DATE( YEAR(_fiscal_year_start) - 1, MONTH(_fiscal_year_start), 01 ) VAR _result = UNION ( ADDCOLUMNS (CALENDAR ( _today_date, _today_date), "Timeframe", "Today", "Timeframe Order", 1 ) , ADDCOLUMNS (CALENDAR ( _yesterday_date, _yesterday_date), "Timeframe", "Yesterday", "Timeframe Order", 2 ) // Week , ADDCOLUMNS (CALENDAR ( _week_start - 7, _week_end - 7 ), "Timeframe", "Previous Week", "Timeframe Order", 3 ) , ADDCOLUMNS (CALENDAR ( _week_start, _week_end ), "Timeframe", "Current Week", "Timeframe Order", 4 ) , ADDCOLUMNS (CALENDAR ( _week_start, _today_date ), "Timeframe", "WTD", "Timeframe Order", 5 ) , ADDCOLUMNS (CALENDAR ( _week_start_py, _today_date_py ), "Timeframe", "WTD Previous Year", "Timeframe Order", 6 ) // Month , ADDCOLUMNS (CALENDAR ( _previous_month_start, _previous_month_end ), "Timeframe", "Previous Month", "Timeframe Order", 7 ) , ADDCOLUMNS (CALENDAR ( _month_start, _month_end ), "Timeframe", "Current Month", "Timeframe Order", 8 ) , ADDCOLUMNS (CALENDAR ( _month_start, _today_date ), "Timeframe", "MTD", "Timeframe Order", 9 ) , ADDCOLUMNS (CALENDAR ( _month_start_py, _today_date_py ), "Timeframe", "MTD Previous Year", "Timeframe Order", 10 ) // Quarter , ADDCOLUMNS (CALENDAR ( _previous_quarter_start, _previous_quarter_end ), "Timeframe", "Previous Qtr", "Timeframe Order", 11 ) , ADDCOLUMNS (CALENDAR ( _quarter_start, _quarter_end ), "Timeframe", "Current Qtr", "Timeframe Order", 12 ) , ADDCOLUMNS (CALENDAR ( _quarter_start, _today_date ), "Timeframe", "QTD", "Timeframe Order", 13 ) , ADDCOLUMNS (CALENDAR ( _quarter_start_py, _today_date_py ), "Timeframe", "QTD Previous Year", "Timeframe Order", 14 ) // Financial Year , ADDCOLUMNS (CALENDAR ( _previous_fiscal_year_start, _previous_fiscal_year_end ), "Timeframe", "Previous Fiscal Year", "Timeframe Order", 15 ) , ADDCOLUMNS (CALENDAR ( _fiscal_year_start_py, _today_date_py ), "Timeframe", "YTD Previous Fiscal Year", "Timeframe Order", 16 ) , ADDCOLUMNS (CALENDAR ( _fiscal_year_start, _fiscal_year_end ), "Timeframe", "Current Fiscal Year", "Timeframe Order", 17 ) , ADDCOLUMNS (CALENDAR ( _fiscal_year_start, _today_date ), "Timeframe", "YTD Fiscal", "Timeframe Order", 18 ) , ADDCOLUMNS (CALENDAR ( _tomorrow_date, _fiscal_year_end ), "Timeframe", "Rest of Fiscal Year", "Timeframe Order", 19 ) // Calendar Year , ADDCOLUMNS (CALENDAR ( _previous_calendar_year_start, _previous_calendar_year_end ), "Timeframe", "Previous Calendar Year", "Timeframe Order", 20 ) , ADDCOLUMNS (CALENDAR ( _previous_calendar_year_start, _today_date_py ), "Timeframe", "YTD Previous Calendar Year", "Timeframe Order", 21 ) , ADDCOLUMNS (CALENDAR ( _calendar_year_start, _calendar_year_end ), "Timeframe", "Current Calendar Year", "Timeframe Order", 22 ) , ADDCOLUMNS (CALENDAR ( _calendar_year_start, _today_date ), "Timeframe", "YTD Calendar", "Timeframe Order", 23 ) , ADDCOLUMNS (CALENDAR ( _tomorrow_date, _calendar_year_end ), "Timeframe", "Rest of Calendar Year", "Timeframe Order", 24 ) ) RETURN _result

Calendar Table

aduguid — Mon, 08 Jul 2024 04:04:30 GMT

Calendar table in DAX

Calendar = VAR _today_date = TODAY() //'Properties'[Today Date] VAR _fiscal_year = YEAR(EDATE( _today_date, 6)) VAR _fiscal_year_start = DATE ( _fiscal_year - 1, 07, 01) VAR _fiscal_year_end = DATE ( _fiscal_year, 06, 30) VAR _result = ADDCOLUMNS ( //CALENDARAUTO() //range of dates is calculated automatically based on data in the model CALENDAR(_fiscal_year_start, _fiscal_year_end) , "Calendar Year Period End", FORMAT([Date], "yyyy12") , "Calendar Year Period Start", FORMAT([Date], "yyyy01") , "Calendar Year Period", FORMAT([Date], "yyyyMM") , "Calendar Year Quarter Nbr", QUARTER([Date]) , "Calendar Year Quarter", FORMAT([Date], "\C\Yyyyy \Qq") , "Calendar Year Half", FORMAT([Date], "\F\Yyyyy \H") & ROUNDUP(MONTH([Date]) / 6, 0) , "Calendar Year", YEAR([Date]) , "Day Name Short", FORMAT([Date], "DDD") , "Day Name", FORMAT([Date], "DDDD") , "Day Of Week", WEEKDAY([Date]) , "Day", DAY([Date]) , "Fiscal Year Period End", FORMAT(EDATE([Date], 6), "yyyy12") , "Fiscal Year Period Start", FORMAT(EDATE([Date], 6), "yyyy01") , "Fiscal Year Period", FORMAT(EDATE([Date], 6), "yyyyMM") , "Fiscal Year Quarter Nbr", FORMAT(EDATE([Date], 6), "q") , "Fiscal Year Quarter", FORMAT(EDATE([Date], 6), "\F\Yyyyy \Qq") , "Fiscal Year Half", FORMAT(EDATE([Date], 6), "\F\Yyyyy \H") & ROUNDUP(MONTH(EDATE([Date], 6)) / 6, 0) , "Fiscal Year", YEAR(EDATE([Date], 6)) , "Is Current FY", IF(YEAR(EDATE([Date], 6)) = _fiscal_year, 1, 0) , "Is Future", IF([Date] > _today_date, 1, 0) , "Is Past", IF([Date] < _today_date, 1, 0) , "Month End", EOMONTH([Date], 0) , "Month Name Short", FORMAT([Date], "MMM") , "Month Name", FORMAT([Date], "MMMM") , "Month Start", DATE(YEAR([Date]), MONTH([Date]), 1) , "Month", MONTH([Date]) , "Week Ending", [Date] + 7 - WEEKDAY([Date], 1) // Saturday , "Week Starting", [Date] - WEEKDAY([Date], 1) + 1 // Sunday , "Week Day", WEEKDAY([Date], 2) , "Week of Month", 1 + WEEKNUM([Date]) - WEEKNUM( EOMONTH([Date], -1 ) + 1 ) , "Week of Year", WEEKNUM([Date]) , "Week of Fiscal Year", IF(MONTH([Date]) < 7 , WEEKNUM([Date], 1) + (WEEKNUM(DATE(YEAR([Date]), 7, 1), 1) - 1) , WEEKNUM([Date], 1) - WEEKNUM(DATE(YEAR([Date]), 7, 1), 1) + 1) ) RETURN _result

Calendar table in M

let //Set the following variables Culture = "English (United States)", //Select a culture. UseYesterdayAsCurrentDate = true, //true = yesterday, false = today YearsBack = 6, //How many years to include prior to the current year. YearsAhead = 6, //How many years to include after the current year. FyYearsBack = 2, //How many fiscal years to include prior to the current year. FyYearsAhead = 2, //How many fiscal years to include after the current year. GoToBeginning = "Year", //Options: Year, Month, None GoToEnd = "Year", //Options: Year, Month, None //Figure the Start and End Dates, based on above variables DateToday = DateTime.Date(DateTime.LocalNow()), CurrentDate = if UseYesterdayAsCurrentDate = true then Date.AddDays(DateToday, -1) else DateToday, YearBegin = Date.Year(CurrentDate) - YearsBack, MonthBegin = if GoToBeginning = "Year" then 1 else Date.Month(CurrentDate), DayBegin = if GoToBeginning = "None" then Date.Day(CurrentDate) else 1, StartDate = #date(YearBegin, MonthBegin, DayBegin), YearEnd = Date.Year(CurrentDate) + YearsAhead, MonthEnd = if GoToEnd = "Year" then 12 else Date.Month(CurrentDate), DayEndTemp = if GoToEnd = "Year" then 31 else Date.Day(CurrentDate), EndDateTemp = #date(YearEnd, MonthEnd, DayEndTemp), EndDate = if GoToEnd = "Month" then DateTime.Date(Date.EndOfMonth(EndDateTemp)) else EndDateTemp, DayCount = Duration.Days(Duration.From(EndDate - StartDate)) + 1, //Get complete list of dates, Convert to a table, update name and data type AllDates = List.Dates(StartDate,DayCount,#duration(1,0,0,0)), TableFromList = Table.FromList(AllDates, Splitter.SplitByNothing()), ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type date}}), RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1", "Date"}}), //Add other attributes of the date, as desired InsertYear = Table.AddColumn(RenamedColumns, "Year", each Date.Year([Date])), InsertQuarterOfYear = Table.AddColumn(InsertYear, "Quarter Of Year", each Date.QuarterOfYear([Date])), InsertMonthOfYear = Table.AddColumn(InsertQuarterOfYear, "Month Of Year", each Date.Month([Date])), InsertDayOfMonth = Table.AddColumn(InsertMonthOfYear, "Day Of Month", each Date.Day([Date])), InsertDayOfWeek = Table.AddColumn(InsertDayOfMonth, "Day Of Week", each Date.DayOfWeek([Date])), InsertDateAlternateKey = Table.AddColumn(InsertDayOfWeek, "Date Alternate Key", each [Year] * 10000 + [Month Of Year] * 100 + [Day Of Month]), InsertMonthName = Table.AddColumn(InsertDateAlternateKey, "Month Name", each Date.ToText([Date], "MMMM", Culture), type text), InsertMonthKey = Table.AddColumn(InsertMonthName, "Month Key", each [Year] * 100 + [Month Of Year]), InsertMonthYear = Table.AddColumn(InsertMonthKey, "Month Year", each (try(Text.Range([Month Name],0,3)) otherwise [Month Name]) & " " & Number.ToText([Year])), InsertMonthStart = Table.AddColumn(InsertMonthYear, "Month Start", each Date.StartOfMonth([Date]), type date), InsertMonthEnd = Table.AddColumn(InsertMonthStart, "Month Ending", each Date.EndOfMonth([Date]), type date), InsertQuarterName = Table.AddColumn(InsertMonthEnd, "Quarter Name", each "Q" & Number.ToText([Quarter Of Year])), InsertQuarterKey = Table.AddColumn(InsertQuarterName, "Quarter Key", each [Year] * 100 + [Quarter Of Year]), InsertQuarterYear = Table.AddColumn(InsertQuarterKey, "Quarter Year", each "Q" & Number.ToText([Quarter Of Year]) & " " & Number.ToText([Year])), InsertDayName = Table.AddColumn(InsertQuarterYear, "Day Of Week Name", each Date.ToText([Date], "dddd", Culture), type text), InsertWeekEnding = Table.AddColumn(InsertDayName, "Week Ending", each Date.EndOfWeek([Date]), type date), //Add Relative Date Positions. InsertRelativeYear = Table.AddColumn(InsertWeekEnding, "Relative Year", each [Year]-Date.Year(CurrentDate)), InsertRelativeYearDescription = Table.AddColumn(InsertRelativeYear, "Relative Year Description", each if [Relative Year] = 0 then "Current Year" else if [Relative Year] = -1 then "Last Year" else if [Relative Year] = 1 then "Next Year" else if [Relative Year] < -1 then Number.ToText(Number.Abs([Relative Year])) & " Years Back" else Number.ToText([Relative Year]) & " Years Ahead"), InsertRelativeQuarter = Table.AddColumn(InsertRelativeYearDescription, "Relative Quarter", each 4*([Year]-Date.Year(CurrentDate)) + ([Quarter Of Year]-Date.QuarterOfYear(CurrentDate))), InsertRelativeQuarterDescription = Table.AddColumn(InsertRelativeQuarter, "Relative Quarter Description", each if [Relative Quarter] = 0 then "Current Quarter" else if [Relative Quarter] = -1 then "Last Quarter" else if [Relative Quarter] = 1 then "Next Quarter" else if [Relative Quarter] < -1 then Number.ToText(Number.Abs([Relative Quarter])) & " Quarters Back" else Number.ToText([Relative Quarter]) & " Quarters Ahead"), InsertRelativeMonth = Table.AddColumn(InsertRelativeQuarterDescription, "Relative Month", each 12*([Year]-Date.Year(CurrentDate)) + ([Month Of Year]-Date.Month(CurrentDate))), InsertRelativeMonthDescription = Table.AddColumn(InsertRelativeMonth, "Relative Month Description", each if [Relative Month] = 0 then "Current Month" else if [Relative Month] = -1 then "Last Month" else if [Relative Month] = 1 then "Next Month" else if [Relative Month] < -1 then Number.ToText(Number.Abs([Relative Month])) & " Months Back" else Number.ToText([Relative Month]) & " Months Ahead"), InsertRelativeWeek = Table.AddColumn(InsertRelativeMonthDescription, "Relative Week", each Duration.Days(Duration.From([Week Ending]-Date.EndOfWeek(CurrentDate)))/7), InsertRelativeWeekDescription = Table.AddColumn(InsertRelativeWeek, "Relative Week Description", each if [Relative Week] = 0 then "Current Week" else if [Relative Week] = -1 then "Last Week" else if [Relative Week] = 1 then "Next Week" else if [Relative Week] < -1 then Number.ToText(Number.Abs([Relative Week])) & " Weeks Back" else Number.ToText([Relative Week]) & " Weeks Ahead"), InsertRelativeDay = Table.AddColumn(InsertRelativeWeekDescription, "Relative Day", each Duration.Days(Duration.From([Date]-CurrentDate))), InsertRelativeDayDescription = Table.AddColumn(InsertRelativeDay, "Relative Day Description", each if [Relative Day] = 0 then "Current Day" else if [Relative Day] = -1 then "Last Day" else if [Relative Day] = 1 then "Next Day" else if [Relative Day] < -1 then Number.ToText(Number.Abs([Relative Day])) & " Days Back" else Number.ToText([Relative Day]) & " Days Ahead"), //Add Date Category Positions. InsertYearGroup = Table.AddColumn(InsertRelativeDayDescription, "Year Group", each if [Relative Year] = 0 then "Current Year" else if [Relative Year] < 0 then "Past Years" else "Future Years"), InsertQuarterGroup = Table.AddColumn(InsertYearGroup, "Quarter Group", each if [Relative Quarter] = 0 then "Current Quarter" else if [Relative Quarter] < 0 then "Past Quarters" else "Future Quarters"), InsertMonthGroup = Table.AddColumn(InsertQuarterGroup, "Month Group", each if [Relative Month] = 0 then "Current Month" else if [Relative Month] < 0 then "Past Months" else "Future Months"), InsertWeekGroup = Table.AddColumn(InsertMonthGroup, "Week Group", each if [Relative Week] = 0 then "Current Week" else if [Relative Week] < 0 then "Past Weeks" else "Future Weeks"), InsertDayGroup = Table.AddColumn(InsertWeekGroup, "Day Group", each if [Relative Day] = 0 then "Current Day" else if [Relative Day] < 0 then "Past Days" else "Future Days"), ChangedType2 = Table.TransformColumnTypes(InsertDayGroup,{{"Year", Int64.Type}, {"Quarter Of Year", Int64.Type}, {"Month Of Year", Int64.Type}, {"Day Of Month", Int64.Type}, {"Day Of Week", Int64.Type}, {"Date Alternate Key", Int64.Type}, {"Quarter Name", type text}, {"Month Year", type text}, {"Quarter Year", type text}, {"Relative Year Description", type text}, {"Relative Quarter Description", type text}, {"Relative Month Description", type text}, {"Relative Week Description", type text}, {"Relative Day Description", type text}, {"Year Group", type text}, {"Quarter Group", type text}, {"Month Group", type text}, {"Week Group", type text}, {"Day Group", type text}, {"Relative Day", Int64.Type}, {"Relative Week", Int64.Type}, {"Relative Month", Int64.Type}, {"Relative Quarter", Int64.Type}, {"Relative Year", Int64.Type}, {"Quarter Key", Int64.Type}, {"Month Key", Int64.Type}}), //Add Fiscal Periods InsertFiscalYear = Table.AddColumn(ChangedType2, "Fiscal Year", each Date.ToText(Date.AddMonths([Date], 6), "yyyy")), InsertPeriod = Table.AddColumn(InsertFiscalYear, "Period", each Date.ToText(Date.AddMonths([Date], 6), "yyyyMM")), InsertPeriodFyStart = Table.AddColumn(InsertPeriod, "Fiscal Year Period Start", each Date.ToText(Date.AddMonths([Date], 6), "yyyy01")), InsertPeriodFyEnd = Table.AddColumn(InsertPeriodFyStart, "Fiscal Year Period End", each Date.ToText(Date.AddMonths([Date], 6), "yyyy12")), InsertFiscalYearQuarterOfYear = Table.AddColumn(InsertPeriodFyEnd, "Fiscal Year Quarter", each Date.QuarterOfYear(Date.AddMonths([Date], 6))) in InsertFiscalYearQuarterOfYear

Service Now Dashboard Template

aduguid — Mon, 08 Jul 2024 03:52:36 GMT

This Power BI template is designed for loading ServiceNow records from the task table using the REST API. It includes bookmarks that allow users to dynamically change the x-axis of trend charts via buttons in the left-side filter pane. Additionally, there are buttons below the charts that enable users to change the date used and adjust the grouping. This template was developed for on-premises Power BI Report Server but also functions in the Power BI Service. It's also in a GitHub project.