Custom Month Start Date and End Date

[Selded]

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

[AlexAlberga727]

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!

[Selded]

@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

[Selded]

@AlexAlberga727 

Please any help with my question?

[AlexAlberga727 · Accepted solution]

@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!

[Selded]

@AlexAlberga727

 Thanks, this gave the expected results.

[Selded]

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

[AlexAlberga727]

What fields are you using to create your visual?

Are you using the column we created?

Share a screen shot of what is happening when you do.

[AlexAlberga727]

@Selded 

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

[AlexAlberga727]

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

[Selded]

@AlexAlberga727

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