DAX for custom start date and end date

[AAAbbbnewbie]

Hi Experts, i would need DAX formula to determine the month for the date with custom start date and end date.

Start date is every 21st of the previous month and end date is every 20th of the current month.

For example, 21 Feb 2023 to 20 Mar 2023 is Mar 2023.

Also i need MTD showing based on the custom start and end date too. Possible to exclude weekend and Public holiday? i have a calendar for these. 

Please help 🙂

[Jihwan_Kim]

Hi,

I am not sure how your datamodel looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

I hope you can add more columns into the calendar table, like weekdays or some others.

Quantity MTD: = 
CALCULATE (
    [Quantity:],
    WINDOW (
        1,
        ABS,
        0,
        REL,
        ALL ( 'Calendar'[Custom Year-Month], 'Calendar'[Date] ),
        ORDERBY ( [Date], ASC ),
        KEEP,
        PARTITIONBY ( 'Calendar'[Custom Year-Month] ),
        MATCHBY('Calendar'[Date])
    )
)

[AAAbbbnewbie]

Thanks Jihwan. The calendar table is very useful for me. But i dont understand the second part MTD. What does quantity stand for?