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

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.

Reply
hellodazza
Helper II
Helper II

Date Filter

Hi Power Bi Masters, 

I am trying to do a filter for dates. There is a LicensePeriodStartDate and LicensePeriodEndDate I can get monthly numbers for one month in power query but don't know how to apply it for every other month. Example for December 2023. 

Start dates will be all dates pre 31 December 2023 will be selected. Anything after will be unchecked. 
End dates will be all dates post 1 December 2023 will be selected. Anything before will be unchecked. 

After this filter I can get the number of hours for the month of December 2023 from a duration column. However how do i do this filter for all the rest of the months in a year? And can be shown on a clustered column chart? Appreciate any help. Thanks.

1 ACCEPTED SOLUTION

You need to use a disconnected calendar table.

 

lbendlin_1-1708138609738.png

 

View solution in original post

10 REPLIES 10
hellodazza
Helper II
Helper II

Hi, 

I have a pbix and excel data sample link here. So I have already set the date filter in power query to get the numbers for December 2023. I get the numbers by totalling up [Duration New]. 

Next is I want to do this same date filter conditions for other months. For example November 2023. 
Start dates will be all dates pre 30 November 2023 will be selected. Anything after will be unchecked. 
End dates will be all dates post 1 November 2023 will be selected. Anything before will be unchecked. 

Problem No.2 is how to visualise this?
For example, in the visuals it is showing 135k for December 2023. But the actual numbers for December 2023 is actually around 1602540. I don't want this number to be spread out through the months. 

I know it is abit confusing but appreaciate any help. Thanks.

Cheers
Darren

 

For example, in the visuals it is showing 135k for December 2023

 

135k what?  hours inside contracts?

 

Here's a first stab, shown by contract start date. This can then be extended over the contract duration once I know what you are calculating.

lbendlin_0-1707258285221.png

 

135k is actually minutes. i am calculating content hours. i do have a simple measure to calculate the hours.

Content Hours = SUM('SVOD Data'[Duration New])/60

appreciate your help. 🙏

I think the calculation will be quite complicated...any help it is appreciated. The numbers for December 2023 should be 1602540 instead of 135k. It is being spread out throughout the months and years. 

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

Hi @lbendlin uploaded a new files here.

If you go into power query editor of my data, there is a filter set on the dates and services. 

Date filter conditions to get December 2023 numbers is:
Start dates will be all dates pre 31 December 2023 will be selected. Anything after will be unchecked. 
End dates will be all dates post 1 December 2023 will be selected. Anything before will be unchecked. 

There is also a filter on the Services. 

After setting this filters, the sum of December 2023 will be 692k. 

My issues are:
1) how do i set the same date filter for each month of the year.
2) how to display it properly. As you can see the 629k is distributed across 2021-2023. I only need the 629k to be displayed for December 2023.  

Appreciate your help.

You need to use a disconnected calendar table.

 

lbendlin_1-1708138609738.png

 

Appreciate your help @lbendlin . I will give it a go and explore the disconnected calendar table. 

I have tested it. it worked for me. @lbendlin thank you sensei. 

lbendlin
Super User
Super User

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors