Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Kumarcy
Helper II
Helper II

Need help with date

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. 4DBC4C5C-40B6-4494-A2C5-D28913075031_4_5005_c.jpeg

Display Previous Start=

DATE(DATEADD('year',-1,[Start Date]))

 

Please help.

 

1 ACCEPTED SOLUTION
v-juanli-msft
Community Support
Community Support

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.

View solution in original post

4 REPLIES 4
v-juanli-msft
Community Support
Community Support

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
Super User
Super User

@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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.