How to show last n months of data in a column chart just using a calendar date table

[Sagejah9]

Hi. I am trying to show the last n months of a calculated measure I created in a bar chart based on the selected month and year in a slicer. Is there a way to do this with just only the calendar date table? 

[Ashish_Mathur]

Hi,

You may refer to my solution here - Flex a Pivot Table to show data for x months ended a certain user defined month.

Hope this helps.

[v-yangliu-msft]

Hi  @Sagejah9 ,

I created some data:

Here are the steps you can follow：

1. Create calculated column.

DateColumn =
DATE(YEAR('Table'[date]),MONTH('Table'[date]),1)

2. Create calculated table.

Table 2 =
DISTINCT('Table'[DateColumn])

3. Change the date format in Column tools.

4. Create measure.

Flag =
var _select=SELECTEDVALUE('Table 2'[DateColumn])
return
IF(
MAX('Table'[date])>=DATE(YEAR(_select),1,1)&&MAX('Table'[date])<=EOMONTH(_select,0),1,0)

5. Use the column [DateColumn] of Table2 as the slicer, place the measure in Filters and set is=1, apply filter.

6. Result:

When 2021.6 is selected, the data before June of the current year is displayed

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[sunielsamal]

This is really cool. But how can it work with Date Hirarchies slicer? 

[Sagejah9]

I would like to show the last n months in this chart. I had to turn off the interaction from the slicer to show all the values. When I try to do the measure to show the last n months, the values show correct from oct to Jan and when Feb is selected the values from oct to Jan change to the same value as Feb. 

[Greg_Deckler]

@Sagejah9 Is your Calendar table the basis for both the slicers and the axis of the measure? Because I don't believe it will work that way, you would need separate, disconnected tables.

[Sagejah9]

hi @Greg_Deckler yes, it would be. But seeing that it cannot work. I created a duplicate of my calendar table and used that on the basis of the axis and the condition passed. And the original calendar for the filters. I did a lookup for the month number of the month slicer and returned all months less than or equal to the selected month's number. I'm not sure if there is a more effective way to do this. 

[Greg_Deckler]

@Sagejah9 I don't know what code you used but essentially that's how I would have done it.

[Sagejah9]

@Greg_Deckler 

SalesMargin (last 5 months) =

var salesmargin =

CALCULATE (

[SalesMargin%],

FILTER (

ALL ( DimDate2[Date]),

DimDate2[Date] <= MAX ( 'DimDate'[Date] )

&& DimDate2[Date] >= EDATE ( MAX('DimDate'[Date]),-5 )

))

return 

salesmargin

Its repeating the value for March for all the months

[Greg_Deckler]

@Sagejah9 Try:

SalesMargin (last 5 months) =
VAR __MaxDate = MAX( 'DimDate'[Date])
VAR __MinDate = EDATE ( __MaxDate,-5 )
var salesmargin =
CALCULATE (
[SalesMargin%],
FILTER (
ALL ( DimDate2[Date]),
DimDate2[Date] <= __MaxDate )
&& DimDate2[Date] >= __MinDate)

[Sagejah9]

@Greg_Deckler 

I tried it. The datapoint is repeated for all months in the visual and changes when I selected a different month. 

[Greg_Deckler]

@Sagejah9 Do you have a relationship defined between DimDate and your fact table?