cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

Custom Month Start Date and End Date

Hi all,

 

Here is a description of the date slicer i need on my report.

I have three customers using a report with customer ID and date as slicers on the report page. Customer 1 and 2 use a calendar month for thier transactions whilst customer 3 uses a custom production month. Example the month of June for Customer 1 and 2 is June 1st 2020-June 30th 2020 whilst for customer 3 is May 25th 2020 to June 26th 2020. How do i modify my date table to achive this ensuring that if customer 3 set the upper bound of the date slicer to May 25th and lower bound to June 26th, the data in the matrix visualization will arrange in this sort order.

 

Thank you all

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Resolver II
Resolver II

Re: Custom Month Start Date and End Date

@Selded 

 

Try this instead for those december days > 25. 

 

Customer #3 Months = 
IF( MONTH(dDateTable[Date]) = 12 && DAY( dDateTable[Date] ) > 25 , 1 ,
IF( DAY(dDateTable[Date]) > 25 , MONTH( dDateTable[Date] ) + 1 , MONTH( dDateTable[Date] )))

 Let me know if that works. Sorry for the late reply. 

 

In regards to how you can filter by month name, opposed to a number ... I tried a handful of attempts trying to utilize the FORMAT function as well as even concatenating the month number to try and make a date to then utilize the format function more effectively, and I was not able to get that working. However, you can always create a table with values 1-12 with their associated month names and enter that into your data model to then bring in that relationship. This would work - However, I was trying to find a solution within DAX. I'm sure there is a way to do so but it may be just easier to create the table as I mentioned above. 

 

Anywho - Hope this helps, Enjoy!

View solution in original post

10 REPLIES 10
Highlighted
Resolver II
Resolver II

Re: Custom Month Start Date and End Date

I may have a way to expand your thinking on how this could be achieved. However, there are so many ways somehting like this could be acheived through DAX im sure.

 

Within my date table I have different filters for different occasions. If I had a customer I had to report to which calls for specific filters I would attempt to create something specifically for them. 

 

As an example I have created calculated columns within my date table to identify "This Month", "Last Month", "Prior Month" so I was able to create ease slicers for the folks reviewing the reporting. 

 

In this case you may be able to find a pattern for you customers and create a calculated column to do the same.

Right off the bat Im thiking somehting along the lines of the following could work for your case :

 

Customer #3 Months = 
IF( DAY([Date])>25 , MONTH([Date]) + 1 , MONTH([Date]))

 

With this you could now create a filter based off of those months. Of course you then could create additional logic to return the month name that the month values are associated with. 

 

Let me know if this would work for you. Also if you come up with an alternative, let me know.

 

Enjoy!

Highlighted
Helper II
Helper II

Re: Custom Month Start Date and End Date

@AlexAlberga727 

This calculated coulmn works fine from January to November. In December, Customer 3 Month becomes November 26th to Decemebr 31st. Is there a way this can also be catered for?

Customer #3 Months = 
IF( DAY([Date])>25 , MONTH([Date]) + 1 , MONTH([Date]))

Also please  assit me with the logic to month name and year based for the filter. 

 

Thank you

Highlighted
Helper II
Helper II

Re: Custom Month Start Date and End Date

@AlexAlberga727 

 

Please any help with my question?

Highlighted
Resolver II
Resolver II

Re: Custom Month Start Date and End Date

Im assuming Jan of the next year would need to be December 26th to Jan 25th, correct?

Highlighted
Helper II
Helper II

Re: Custom Month Start Date and End Date

@AlexAlberga727

 

January next year will be January 1st to January 25th and December this year will be November 26th to December 31st.

Highlighted
Resolver II
Resolver II

Re: Custom Month Start Date and End Date

@Selded 

 

Try this instead for those december days > 25. 

 

Customer #3 Months = 
IF( MONTH(dDateTable[Date]) = 12 && DAY( dDateTable[Date] ) > 25 , 1 ,
IF( DAY(dDateTable[Date]) > 25 , MONTH( dDateTable[Date] ) + 1 , MONTH( dDateTable[Date] )))

 Let me know if that works. Sorry for the late reply. 

 

In regards to how you can filter by month name, opposed to a number ... I tried a handful of attempts trying to utilize the FORMAT function as well as even concatenating the month number to try and make a date to then utilize the format function more effectively, and I was not able to get that working. However, you can always create a table with values 1-12 with their associated month names and enter that into your data model to then bring in that relationship. This would work - However, I was trying to find a solution within DAX. I'm sure there is a way to do so but it may be just easier to create the table as I mentioned above. 

 

Anywho - Hope this helps, Enjoy!

View solution in original post

Highlighted
Resolver II
Resolver II

Re: Custom Month Start Date and End Date

@Selded 

Just saw your reply. Change the 1 to 12 in the first IF statement to get your expected results.

Highlighted
Helper II
Helper II

Re: Custom Month Start Date and End Date

@AlexAlberga727

 Thanks, this gave the expected results.

Highlighted
Helper II
Helper II

Re: Custom Month Start Date and End Date

@AlexAlberga727

 

I got the month numbers correctly, but i am stuck as to how to apply it on my visuals. I inserted the table but is not working as expected. 

 

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors