SHOW LATEST 3 MONTH IN BAR GRAPH

[icdns]

Hi guys, 

Could somebody help me in displaying the LATEST 3 MONTHS data that will show in my bar graph. I have my DIM_CALENDAR and Date_Key as my dimension which is connected to my Date column in my FCT table 

Example scenario: 

My 2020 data has January, February, March and April. 

If I filter 2020 in my YEAR filter, it will show the latest 3 months (Feb, March & April)

Note: It is only dependent on my YEAR filter

Ex. 

Filter: 2020

Jan - 95%

Feb - 98%

Mar - 92%

April - 91%

Result Visual - (Display in Bar) :

Feb - 98%

Mar - 92%

April - 91%

I have used this formula: 

LATEST_3_MONTHS = CALCULATE(sum(FCT_TABLE[ PERCENTAGE ]),DATESINPERIOD('DIM_CALENDAR'[FullDateAlternateKey],MAX(FCT_TABLE[DATE]),-3,MONTH))

But it is not working 😞

Thank you!

[Greg_Deckler]

@icdns - Can you just use a relative date filter? Last 3 months?

[icdns]

Hi, Greg_Deckler, 

I only have one filter which is the year 😞

[amitchandak]

@icdns , and year filter is from the date Table?  I am assuming the second reply does not work.

Can you share sample data and sample output in table format?

[icdns]

Hi amitchandak, 

yes my year is in my date dimension table. 

Ohh im afraid it is on direct query 😞

[ibarrau]

Hi. When I want to show last 3 months of the selected period I usually use this:

Measure L3M = 
VAR _t = MAX('Date'[Date])
VAR _End = EOMONTH( _t , 0 )
VAR _Start = EOMONTH( _t , -3 ) +1
RETURN 
CALCULATE (
    [Measure],
    DATESBETWEEN( 'Date'[Date] , _Start , _End )
)

Hope this helps, 

[icdns]

Hi, ibarrau 

I'm sorry for the confusion but it is NOT affected by my month filter. Only with the YEAR filter. So whatever my year is, it will only show the latest 3 months of data for that year. 

Thanks! 🙂

[amitchandak]

@icdns , Try like

LATEST_3_MONTHS =
var _max = calculate(MAX(FCT_TABLE[DATE]), filter(all(Date), Date[year] =selectedvalue(Date[Year])))
return
CALCULATE(sum(FCT_TABLE[ PERCENTAGE ]),DATESINPERIOD('DIM_CALENDAR'[FullDateAlternateKey],_max,-3,MONTH))

[amitchandak]

@icdns , This formula seems correct to me

Hope FullDateAlternateKey is date column ?

DIM_CALENDAR is marked as date table ?

DIM_CALENDAR has all the required Dates?

LATEST_3_MONTHS = CALCULATE(sum(FCT_TABLE[ PERCENTAGE ]),DATESINPERIOD('DIM_CALENDAR'[FullDateAlternateKey],MAX(FCT_TABLE[DATE]),-3,MONTH))

Try to select a month using slicer and check if it works.

if it does not work

Can you share a sample pbix after removing sensitive data.

[icdns]

Hi, amitchandak

To answer your questions: 

Hope FullDateAlternateKey is date column ? yes

DIM_CALENDAR is marked as date table ? yes

DIM_CALENDAR has all the required Dates? yes

I just thinked that it only depends on my YEAR filter, not month filter. 

Whatever my year is. it will only show the latest 3 months of data. 

Can I still use this formula?