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
markefrody
Post Patron
Post Patron

Filtering/Slicing Within Different Date Categories

Hi guys,

I’m trying to create a year/month, year/week, and year/month/day slicer wherein it will:
1.) Display all entries as per filter.
2.) Will calculate the hours and minutes and seconds per day for that filter
The problem is the data I have does not have weekly or daily data. It is only giving me value for a date range.

Capture 1.PNG


What I want is similar to the below (Note: Please exclude Sundays):

Capture 2.PNG

Example:
If I filter: November 2019, it will only show me the month, weeks and days for that filter with their corresponding hour, minutes, and seconds.

 

As you can see, if I filter for November 2019 it will show all entries wherein the month/year November 2019 is in or in between the “StatusChangeDateFinal” (start date) and StatusDateEnd (end). It will exclude in the calculation of hr, min, and sec of all data which are not November 2019. 

 

The "Segment Hours", “SegmentMinutes” and “SegmentSeconds” only computes the November 1, 2019 to November 30, 2019 range only. It will not include any dates before Nov 1, 2019 or after November 30, 2019. 

 

Is there a way to filter like this and at the same time compute the hours, minutes and seconds as date range (yr/mo, yr/week, and yyyy/mm/dd) selected? I know that I don't have a week or daily entry but is it possible to distribute the total values equally to monthly, weekly, and daily excluding Sundays.

 

I have attached the raw data ("Raw Data" tab) and the data obtained when Nov 2019 is used ("November 2019" tab) for reference.

Sample Data

 

Greatly appreciate your kind assistance. Thanks!


Best regards,
Mark V.

 

6 REPLIES 6
v-gizhi-msft
Community Support
Community Support

Hi,

 

Please try to create a seperate date slicer table first:

Calendar = CALENDAR(MIN('Table'[StatusDateEnd]),MAX('Table'[StatusDateEnd]))

Then try these three measures:

Segment Hours = 
var _min = MIN('Calendar'[Date])
var _max = MAX('Calendar'[Date])
return
IF(MAX('Table'[StatusDateEnd])>=_min&&MAX('Table'[StatusDateEnd])<=_max,DATEDIFF(_min,_max,SECOND)/3600,0)

Segment Minutes = 
var _min = MIN('Calendar'[Date])
var _max = MAX('Calendar'[Date])
return
IF(MAX('Table'[StatusDateEnd])>=_min&&MAX('Table'[StatusDateEnd])<=_max,DATEDIFF(_min,_max,SECOND)/60,0)

Segment Seconds = 
var _min = MIN('Calendar'[Date])
var _max = MAX('Calendar'[Date])
return
IF(MAX('Table'[StatusDateEnd])>=_min&&MAX('Table'[StatusDateEnd])<=_max,DATEDIFF(_min,_max,SECOND),0)

When you select a date range in slicer, it shows:

10.PNG

Here is my test pbix file:

pbix 

Hope this helps.

 

Best Regards,

Giotto

 

@v-gizhi-msft 

Hi Giotto. Thank you for providing your proposed solution. I have tried your solution but it is showing most of the latest StatusDateEnd as "0". I'm unsure what I am doing wrong or missing something. Have attached my pbix file for reference.

 

pbix file

Hi,

 

It is because when converting 'Calender'[Date] date/time format to date format, the date is end at the max date 00:00:00 AM not 24:00:00 AM.

So the Calender table excludes max date(etc. 2020/4/14 does not exist in this case).

Please try to add one day to the MAX('Calendar'[Date]), like below shows:

 

SegmentHours (new) =
var _min = MIN('Calendar'[Date])
var _max = MAX('Calendar'[Date])+1
return
IF(MAX('PowerAppsStatusHistoryFinalDuration'[StatusDateEnd])>=_min&&MAX('PowerAppsStatusHistoryFinalDuration'[StatusDateEnd])<=_max,DATEDIFF(_min,_max,SECOND)/3600,0)
 
Hope this helps.
 
Best Reagrds,
Giotto

Hi Giotto (@v-gizhi-msft ),

 

Thank you for your help. I made it to work but the output I want is not reflected. It seems I have not explained the output that I wanted clearly. I am very sorry for that. I made a new thread wherein I explained step by step what I need. You can find it here:
https://community.powerbi.com/t5/Desktop/Auto-Calculate-When-Applying-Time-Slicers/td-p/1033489

Appreciate if you can help me with my request.

Best regards,
Mark

 

amitchandak
Super User
Super User

@markefrody 

I have solved a similar problem for a few days. See if that if sample file can help

https://www.dropbox.com/s/yuv64v0cneseghx/value%20Split%20between%20months%20start%20end%20date.pbix...

@amitchandak - I am trying to recreate the example you have given me but I am having difficulties. Will you be able to assist me to recreate it using pbix file in the link below.

 

pbix file

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.