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.
Hello, i've been trying to figure this out for a week now with no luck.
I have 3 bookmarks for time period (6 months, 1 year & 2 year). When I select my bookmark, the current employee id resets. I want to be able to select an employee, then use the bookmark for the time period w/out having to find the employee id again.
In the picute below, i am reviewing employee id 112233 (John Doe), when i select the bookmark time period, it reset to an employee ID no longer in my data source.
i've tried multiple bookmarks e.g selected visual, all visual. i am giving up 😞
Solved! Go to Solution.
Hi @Coryanthony
There are different ways to do it. Please find below a starting point. In the end to really help you we need to see your data model with input sample data and expected output incl. your definition of last 6 months. For example to you calculate it on day or month granularity etc.
Time Intelligence | L6M =
VAR __ReferenceDate = MAX(Dim_Date[Date])
VAR __StartDate = EDate(__ReferenceDate,-6) + 1
VAR __Table = FILTER(ALL(Dim_Date), [Date] >=__StartDate && [Date]<=__ReferenceDate)
VAR __Result = SUMX(__Table,SUM(Fact_Sales[Value]))
RETURN
__Result
or
Time Intelligence | L6M =
var var_ReferenceDate = LASTDATE('Dim Date'[Date])
var var_StartDate = STARTOFMONTH(DATEADD(var_ReferenceDate ,-5,MONTH))
RETURN
CALCULATE(
SUM(FactSales[Value]),
DATESBETWEEN('Dim Date'[Date],var_StartDate,var_ReferenceDate)
)
Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Appreciate your thumbs up!
@ me in replies or I'll lose your thread.
Hi @Coryanthony
There are different ways to do it. Please find below a starting point. In the end to really help you we need to see your data model with input sample data and expected output incl. your definition of last 6 months. For example to you calculate it on day or month granularity etc.
Time Intelligence | L6M =
VAR __ReferenceDate = MAX(Dim_Date[Date])
VAR __StartDate = EDate(__ReferenceDate,-6) + 1
VAR __Table = FILTER(ALL(Dim_Date), [Date] >=__StartDate && [Date]<=__ReferenceDate)
VAR __Result = SUMX(__Table,SUM(Fact_Sales[Value]))
RETURN
__Result
or
Time Intelligence | L6M =
var var_ReferenceDate = LASTDATE('Dim Date'[Date])
var var_StartDate = STARTOFMONTH(DATEADD(var_ReferenceDate ,-5,MONTH))
RETURN
CALCULATE(
SUM(FactSales[Value]),
DATESBETWEEN('Dim Date'[Date],var_StartDate,var_ReferenceDate)
)
Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Appreciate your thumbs up!
@ me in replies or I'll lose your thread.
Hi,
I think you have to unselect the bookmark option 'data'.
Regards
Hi Rudy,
Whenever i unselect the option 'data', it no longer recognize the date filter. It does not apply the date filter when 'data' is unselected.
Hi @Coryanthony
It is only possible to store the whole filter context in the bookmark (Data activated) or no of the filter context (Data inactivated).
If you want to show last year etc. the typical way would be to implement this in your measure logic using time intelligence functions with or without slicers depending on your needs
Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Appreciate your thumbs up!
@ me in replies or I'll lose your thread.
Thank you Michael. Do you have any suggestion on how i can make this work? I am wanting to see employee expenses in a 6month, 1year and 2 year period.
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 |
---|---|
111 | |
94 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |