Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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:
Solved! Go to 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.
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.
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/
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.
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))
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
)
)
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!