Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi Power Users,
I'm learning power bi. I'm trying to achive below.
I need to create 2 slicers for dates, one is start date and second one is end date. The user can change them.
Start date: Month start of the date 1 year prior to the End Date – can be changed by user
End Date: Month end of prior month compared to current month – can be changed by user
Now i have to use these 2 selected dates and need to calculate the following. these calculations i have done in tableau. i need to convert them into powerbi.
Comparison Date=
COUNTD(IF ([Date of Arrival] <= DATEADD('year',-1,[End Date]) AND [Date of Arrival] >= dateadd('year',-1,[Start Date])) then count([ID] end)
Start Date=
COUNTD(IF ([Date of Arrival] <= [End Date] AND [Date of Arrival] >= [Start Date]) then count([ID])end)
then count([ID]) end)
In above calculation i want to show count of id in table and date range in card.
Display Previous Start=
DATE(DATEADD('year',-1,[Start Date]))
Please help.
Solved! Go to Solution.
Hi @Kumarcy
You could create measures in Power BI as below
Comparison Date= calculate(count([ID]),filter(table, [Date of Arrival]<= DATEADD('year',-1,[End Date]) && [Date of Arrival] >= dateadd('year',-1,[Start Date]))
Start Date=calculate(count([ID]),filter(table, [Date of Arrival] <= [End Date] && [Date of Arrival] >= [Start Date]))
But It doesn't support for slicers to change based on other slicers in Power BI.
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Kumarcy
You could create measures in Power BI as below
Comparison Date= calculate(count([ID]),filter(table, [Date of Arrival]<= DATEADD('year',-1,[End Date]) && [Date of Arrival] >= dateadd('year',-1,[Start Date]))
Start Date=calculate(count([ID]),filter(table, [Date of Arrival] <= [End Date] && [Date of Arrival] >= [Start Date]))
But It doesn't support for slicers to change based on other slicers in Power BI.
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Kumarcy , Not very clear.
Refer to this example of two slicers on the same date.
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601
@amitchandak : If user select end date 5/1/2020 then the start date slicer should show last year values only(All months of 2019).
Start date: Month start of the date 1 year prior to the End Date – can be changed by user
End Date: Month end of prior month compared to current month – can be changed by user
when user select start date and end date then i have to calculate below 2 measures.
Comparison Date=
COUNTD(IF ([Date of Arrival] <= DATEADD('year',-1,[End Date]) AND [Date of Arrival] >= dateadd('year',-1,[Start Date])) then count([ID] end)
Start Date=
COUNTD(IF ([Date of Arrival] <= [End Date] AND [Date of Arrival] >= [Start Date]) then count([ID])end)
then count([ID]) end)
@Kumarcy , while you can fets dates you want based on the slicer. But I doubt you will be able to change slicer like that
User | Count |
---|---|
128 | |
108 | |
100 | |
64 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |