Last N Months from Selected Date in a Market Share Trend Graph

[Anonymous]

Hi everyone.

I'm having some troubles making a Graph with last N Months from a selected date in a slicer. I tried to follow some recomendations posted in this forum and I post from Parker Stevens (https://bielite.com/blog/last-12-months-from-selected-date-trailing-12-months-ttm/) but it's not working for me, I think is something related with the MARKET_SHARE variable because if I try to make it with a simple SUM of Values it works, here is my DAX formula:

Market Share 18 Months =

VAR MARKET_SHARE =

DIVIDE(

[Values],

CALCULATE(

[Values],

ALL(General[Laboratory]),

ALL(CHBrand[Brand]),

ALL(General[Product]),

ALL(General[Pack])

),

BLANK()

)

VAR CURRENT_DATE =

SELECTEDVALUE(

'Date'[Date],

MAX('Date'[Date])

)

VAR PREVIOUS_DATE =

DATE(

YEAR(CURRENT_DATE),

MONTH(CURRENT_DATE)-18,

DAY(CURRENT_DATE)

)

RETURN

CALCULATE(

MARKET_SHARE,

FILTER(

General,General[Date] >= PREVIOUS_DATE && General[Date] <= CURRENT_DATE

)

)

I'm not able to get the desire result, as you can see I'm getting more than 18 Months:

Thanks in advance!

[Anonymous · Accepted solution]

I manage to solve the issue by using a variable like this:

Market Share (Units) 13 Months = 
VAR CURRENT_DATE =
    SELECTEDVALUE
    (
        'Date'[Date],
        MAX('Date'[Date])
    )
VAR PREVIOUS_DATE =
    EDATE
    (
        CURRENT_DATE,
        -13
    )

VAR TOTAL =
CALCULATE
(
    [Units],
    ALL('Flat Files FlexView'[Laboratory]),
    ALL(Brands[Brand]),
    ALL('Flat Files FlexView'[Product]),
    ALL('Flat Files FlexView'[Pack]),
    ALL('Flat Files FlexView'[Bricks]),
    ALL('Flat Files FlexView'[Territory]),
    ALL('Flat Files FlexView'[Regions]),
    DATESMTD('Flat Files FlexView'[Date])
)

RETURN
    CALCULATE
    (
        DIVIDE
        (
            [Units],
            TOTAL,
            BLANK()
        ),
        FILTER
        (
            'Flat Files FlexView',
            'Flat Files FlexView'[Date] >= PREVIOUS_DATE && 'Flat Files FlexView'[Date] <= CURRENT_DATE
        )
    )

Now I'm getting the desire result.

[amitchandak]

You have used all at few places on General. Create a date table join with you date and move these date filter there

RETURN
CALCULATE(
MARKET_SHARE,
FILTER(
Date,Date[Date] >= PREVIOUS_DATE && Date[Date] <= CURRENT_DATE
)
)

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

[Anonymous]

Thank you so much @amitchandak 

I tried that before and didn't work for me.

I have a calendar table but I created a Date table separate from the other as Parker Steves (https://bielite.com/blog/last-12-months-from-selected-date-trailing-12-months-ttm/) and I'm not getting my info filtered.

[amitchandak]

I typically use rolling dates like this 

Rolling 18 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-18,MONTH))
Rolling 18 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date Filer],MAX(Sales[Sales Date]),-18,MONTH))

[amitchandak]

Also Try to avoid variable when not needed

Market Share 18 Months =
	
VAR CURRENT_DATE = maxx(allselected('Date'),'Date'[Date])

VAR PREVIOUS_DATE = minx(allselected('Date'),dateadd('Date'[Date],-18,month))

RETURN
CALCULATE(
DIVIDE(
	[Values],
		CALCULATE(
		[Values],
		ALL(General[Laboratory]),
		ALL(CHBrand[Brand]),
		ALL(General[Product]),
		ALL(General[Pack])
		),
	BLANK()
	),
FILTER(
General,General[Date] >= PREVIOUS_DATE && General[Date] <= CURRENT_DATE
)
)

[Anonymous]

Thank you very much @amitchandak 

I tried as you said and now I'm getting this:

I think it's a problem with the calculation of Market Share, because in order to get the right information I need to use all the filters I include (Laboratory, Brand, etc.) but at the moment I try to filter by date I get this result.

What I want is to build a dashboard that when a choose a date in the slicer date, all my graphs show me N months in the past.

Thank you again!