Skip to main content
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I
Helper I

Custom Year Period X Axis that shows Last Year Last Period

Hi All,

I have a requirement to show on X-Axis Last Period of last Year plus all periods of current year … i.e.,Screenshot here
YearPeriod Desired Output

YearPeriod Desired Output.JPG

I already have a Column year period in format ex: "2021 P01"  that is coming from the SQL server table.
I have a single selector slicer on Fiscal Year i.e., 2020,2021,2022, etc.,
so based on Year selected, I should show this X-Axis dynamically to display only the last period of the previous year and all periods of the current year.

So Far I am able to get the last period last year value dynamically based on year slicer selection however I am unable to pass this as a filter to show as desired.



CustomYearPeriod = VAR MonthN = If(dim_date[CalendarMonth] < 10, "0" & dim_date[CalendarMonth],dim_date[CalendarMonth])
VAR TargetYear = VALUE(dim_date[FiscalYear])
VAR PreviousYr = TargetYear-1
Var MaxPeriod = Max(dim_date[CalendarMonth])
VAR Result =  CALCULATE(Min(dim_date[CalendarYearPeriod]),FILTER(dim_date,dim_date[CalendarYearPeriod] >=
PreviousYr &" P" & MaxPeriod))
Return Result




Solution Sage
Solution Sage

Create a new column in your calendar table and fill it in with 2 values. One value will mean "the last period last year or all periods of this year up to and including current", the other will mean the contrary. Then use this column to filter your visual/page/report via the Filter Pane.


Alternatively, use Calculation Groups to change all measures in one go so that they display BLANK when the item on the x-axis is not what you want. 

Helpful resources

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors