Simple DAX Time Filter

[ShrimpSurf]

I want to display a bar chart for revenue so when the user select June, 2021 the chart would only show six months.  If they choose December, 2021, then all twelve months are shown.  I have filters for both month and year.  Any help would be greatly appreciated.

[Ashish_Mathur]

Hi,

See if my solution here helps - Flex a Pivot Table to show data for x months ended a certain user defined month.

[AllisonKennedy]

@ShrimpSurf  This is not exactly what you're looking for, but may give you some ideas and inspiration: https://excelwithallison.blogspot.com/2021/09/power-bi-forecasting-with-irregular.html 

If you share sample data (upload to OneDrive and share link) we can provide more specific measures and help for you, but you'll need to either generate a parameter table OR somehow build into the measure a condition that tells Power BI whether to display 6 months or 12 months, and accounts for all years in your data model. 

Parameter table would be best I think, so basically as @Greg_Deckler  said, a disconnected table with no relationships to your DimDate or data model that has:

Date | Number Months to Display

June 30, 2021 | 6

December 31, 2021 | 12

June 30, 2022 | 6

....

Then create a DAX measure that uses the parameter table to manipulate the measure: 

Filtered Measure =
VAR _startDate = SELECTEDVALUE (Parameter[Date] )

VAR _duration = SELECTEDVALUE Parameter[Number Months to Display] )

RETURN 
CALCULATE ( [Measure] , DATESINPERIOD( DimDate[Date], _startDate, -1*  _duration, Month)

[Greg_Deckler · Accepted solution]

@ShrimpSurf I'm fairly certain that you are going to want a disconnected date table for your slicer. Then you can compare the value selected (MAX or SELECTEDVALUE) with the MAX of the actual date table in the axis of your chart visual and determine whether to return a value in your measure or BLANK().