MTD,YTD Calculation on Non-Standard Date tables

[Anonymous]

Below is my date table, this is non standard date table of Fiscal year. So, i need to calculate MTD, YTD, Today, yesterday sales in power bi desktop.
Example of my dates for Aug month(FY 23) - {Start date - 31/7/22 and End date - 27/8/22}
FY - 23 start date is 03/7/2022 and end date is 01/07/2023. 

Measures I've created:

MTD = CALCULATE(SUM(Sales[TotalCollectedRevenue]),DATESMTD('FiscalCalendar'[Date])

YTD = CALCULATE(sum('Sales'[TotalCollectedRevenue]),DATESYTD(FiscalCalendar[Date],"7/01"))

Please help me to create MTD, YTD sales in power bi

[Anonymous]

@SpartaBI ,

It's not about creating a proper date table, My project FY date table is the same i shown in the figure. 
The New FY calendar is provided by client side. So I've created that type of date table. Please See below the calendar of start date and end date for each month of entire FY-23. So I've created entire date table based on this.

[RicoZhou2 · Accepted solution]

Hi @Anonymous ,

I think you can try measures as below to calcualte MTD and YTD.

MTD =
CALCULATE (
    SUM ( Sales[TotalCollectedRevenue] ),
    FILTER (
        ALL ( 'Sales' ),
        Sales[Date] >= SELECTEDVALUE ( 'FiscalCalendar'[StartDate] )
            && Sales[Date] <= SELECTEDVALUE ( 'FiscalCalendar'[EndDate] )
    )
)

YTD =
CALCULATE (
    SUM ( Sales[TotalCollectedRevenue] ),
    FILTER (
        ALL ( 'Sales' ),
        YEAR ( Sales[Date] ) = SELECTEDVALUE ( 'FiscalCalendar'[FiscalYear] )
    )
)

Best Regards.

[SpartaBI]

@Anonymous do you want to do a zoom call and show me?

[Anonymous]

@SpartaBI 
Okay, i can show you. This is 445 calendar

[SpartaBI]

@Anonymous please join now:
https://us05web.zoom.us/j/87334819323?pwd=MHVtak8zVmZUNHlLWXF2MkZ3WHY1UT09

[Anonymous]

My date table in not in continues date range so that's why I'm unable to calculate MTD, YTD so on.....
Iam not able to use time intellegence functions with this date table.
Below is my date table and it's there for august month, so aug month contains july month's last date that is 31/7/22 and in the aug month, dates there till 27/8/22 only.

[SpartaBI]

@Anonymous so why won't you create a proper date table so you could use time intelligence functions? In the articles there is code you could copy paste to create it.

[SpartaBI]

@Anonymous hey, everything you need is in this article:
https://www.daxpatterns.com/standard-time-related-calculations/

This patten also support fiscal calendars with the limitations indicated there.
If your data does have these limitations then you can use this patten:
https://www.daxpatterns.com/custom-time-related-calculations/

This is the overview page with explanations of the different patterns:
https://www.daxpatterns.com/custom-time-related-calculations/

      

Showcase Report – Contoso By SpartaBI