topic Re: Custom Month Start Date and End Date in DAX Commands and Tips

Custom Month Start Date and End Date

Anonymous — Fri, 03 Jul 2020 05:17:44 GMT

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

Re: Custom Month Start Date and End Date

AlexAlberga727 — Fri, 03 Jul 2020 15:22:09 GMT

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!

Re: Custom Month Start Date and End Date

Anonymous — Fri, 03 Jul 2020 17:25:16 GMT

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

Re: Custom Month Start Date and End Date

Anonymous — Thu, 09 Jul 2020 22:02:14 GMT

AlexAlberga727

Please any help with my question?

Re: Custom Month Start Date and End Date

AlexAlberga727 — Mon, 13 Jul 2020 13:43:41 GMT

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

Re: Custom Month Start Date and End Date

Anonymous — Mon, 13 Jul 2020 13:58:46 GMT

AlexAlberga727

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

Re: Custom Month Start Date and End Date

AlexAlberga727 — Mon, 13 Jul 2020 14:00:55 GMT

Anonymous

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!

Re: Custom Month Start Date and End Date

AlexAlberga727 — Mon, 13 Jul 2020 14:04:46 GMT

Anonymous

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

Re: Custom Month Start Date and End Date

Anonymous — Mon, 17 Aug 2020 13:12:35 GMT

AlexAlberga727

Thanks, this gave the expected results.

Re: Custom Month Start Date and End Date

Anonymous — Tue, 25 Aug 2020 11:29:43 GMT

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.

Re: Custom Month Start Date and End Date

AlexAlberga727 — Tue, 25 Aug 2020 13:19:48 GMT

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.