Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
99 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |