Need help with date

[Kumarcy]

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.

[v-juanli-msft · Accepted 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.

[amitchandak]

@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

[Kumarcy]

@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)

[amitchandak]

@Kumarcy , while you can fets dates you want based on the slicer. But I doubt you will be able to change slicer like that