Date Filter

[hellodazza]

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.

[hellodazza]

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

[lbendlin]

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.

[hellodazza]

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. 🙏

[hellodazza]

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. 

[lbendlin]

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

[hellodazza]

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.

[lbendlin · Accepted solution]

You need to use a disconnected calendar table.

[hellodazza]

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

[hellodazza]

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

[lbendlin]

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...