Specific Date Range on x-axis for clusterd column chart after calculating rolling 12 months

[MB2746]

Hi all,

i am rather new to PowerBI. After reading in this forum for a bit i  created a measure for the rolling 12 months as followed:

StartDate =

VAR DateFrom = MAX('Table'[Date])

VAR numMonthsBack = 12

VAR StartDate = EOMONTH(DateFrom,(numMonthsBack)*-1)+1

RETURN StartDate

EndDate =

VAR DateFrom = MAX('Table'[Date])

VAR numMonthsBack = 0

VAR EndDate = EOMONTH(DateFrom,numMonthsBack)

RETURN EndDate

Rolling 12 month =

VAR rollmonthstart = [StartDate]

VAR rollmonthend = [EndDate]

VAR tmpTable = ALL('Calendar')

VAR tmpTable1 = FILTER(tmpTable,[Date]>=rollmonthstart&&[Date]<=rollmonthend)

Return CALCULATE([TotalSales],tmpTable1)

The issue Im facing now is that i only want to  display the date range from the startdate up to the enddate on the x-axis of the chart so the user can see the per month values that went into the rolling 12 months.

As example the user selects Product 1 in the slicer. The [startdate] is 01.06.2021 and the [enddate] is 31.05.2022. The x-axis should then display the dates from 01.06.2021 until 31.05.2022 in month-year form if possible. 

I have tried different combinations of the Calendar table and dates from the Data table but i cant figure out how to only display these values on the x-axis. Is this possible without adding an additional date range slicer?

I hope i posted all the informationen needed to answer the question. If not i will add it.

[MB27461]

Hi @Greg_Deckler ,

sadly i lost the access to the other account and cant mark your answer as solution. But thank you very much for the answer. 

What i did in the meantime was to create a table with the name helptable  by summarizing the products and the  measures Startdate and EndDate as columns.

Then i created the measure:

IF(MAX('Calendar'[Date])>=(MAX('Table'[StartDate])) 

&&

       MAX('Calendar'[Date])<=MAX('Table'[EndDate]),"True","False")

Afterwards i used this in the visual as filter to only show the months that I need. With this i can also see empty months by using the calendar table as x-axis.

[Greg_Deckler · Accepted solution]

@MB2746 Assuming you have some kind of measure that you are using in the visual, create the following measure and use it in your visual instead:

Measure = 
  VAR __Start = [Start Date]
  VAR __End = [End Date]
  VAR __Value = [Your Current Measure]
  VAR __Date = MAX('Table'[Date])
  VAR __Result = IF( __Date >= __Start && __Date <= __End, __Value, BLANK())
RETURN
  __Result