Date Periods MTD Automation DAX not working properly

[mclawler]

I'm sure this is super simple but I have tried every variation of every DAX I can think of with no success.  I made this report prior to having knowledge of Master Calendar tables and such, so this Date Periods table was my workaround at the time and other than this issue, works flawlessly for everything else in the report.  

My first Column for MTD has to be manually updated to -6(June) or -7(May) etc for each month, and I would just like a simple DAX that automates some sort of Today() measure to always display MTD figures.  My YTD function works great, not sure why I can't use that same sort of DAX for MTD but it doesn't work, QTD doesn't work either.  I've tried datesinperiod, datesbetween, etc. and nothing else seems to work. Just can't seem to find the perfect DAX.  Thank you for your help.  I'd really llike to not have to redo the entire report, as it spans multiple pages.  The result should match the Funded MTD KPI Metric visual if working properly. 

Date Periods =

Union(

    ADDCOLUMNS(

        DATESMTD(dateadd('PwBi OpenClose'[FundedDate].[Date],-6,MONTH)), "Type", "MTD", "Order", 1

        ),

    ADDCOLUMNS(

        DATESQTD('PwBi OpenClose'[FundedDate].[Date]), "Type", "QTD", "Order", 2

        ),

    ADDCOLUMNS(

        DATESYTD('PwBi OpenClose'[FundedDate].[Date]), "Type", "YTD", "Order", 3

        ),

    ADDCOLUMNS(

        PREVIOUSMONTH(DATESMTD('PwBi OpenClose'[FundedDate].[Date])), "Type", "Last Month", "Order", 4

        ),

    ADDCOLUMNS(

        Previousquarter(DATESQTD('PwBi OpenClose'[FundedDate].[Date])), "Type", "Last QTR", "Order", 5

        ),

    ADDCOLUMNS(

        PREVIOUSYEAR(DATESYTD('PwBi OpenClose'[FundedDate].[Date])), "Type", "Last Year", "Order", 6

        ),

    ADDCOLUMNS(

        DATESINPERIOD('PwBi OpenClose'[FundedDate].[Date], TODAY() - 30, 30, Day), "Type", "Last 30 Days", "Order", 7

        ),

    ADDCOLUMNS(

        DATESINPERIOD('PwBi OpenClose'[FundedDate].[Date], TODAY() - 90, 90, Day), "Type", "Last 90 Days", "Order", 8

        ),

    ADDCOLUMNS(

        DATESINPERIOD('PwBi OpenClose'[FundedDate].[Date], EOMONTH(TODAY(), -1), -13, MONTH), "Type", "Lookback 1 Year", "Order", 9

        ),

    ADDCOLUMNS(

        CALENDAR(MIN('PwBi OpenClose'[FundedDate].[Date]), MAX('PwBi OpenClose'[FundedDate].[Date])), "Type", "Custom", "Order", 10

        ),

    ADDCOLUMNS(

        DATESINPERIOD('PwBi OpenClose'[FundedDate].[Date], EOMONTH(TODAY(), -1), -3, MONTH), "Type", "Last 3 Months", "Order", 11

        ),

    ADDCOLUMNS(

        DATESINPERIOD('PwBi OpenClose'[FundedDate].[Date], EOMONTH(TODAY(), -1), -9, MONTH), "Type", "Last 9 Months", "Order", 12

        )

)

[v-yanjiang-msft]

Hi @mclawler ,

According to your description, I create a sample.

PwBi OpenClose table:

Here's my solution.

1.Create a new date table, mark this table as date table.

Date =
FILTER ( 'PwBi OpenClose', 'PwBi OpenClose'[FundedDate] <= TODAY () )

2. Create another table.

Date Periods =
UNION (
    ADDCOLUMNS ( DATESMTD ( 'Date'[FundedDate] ), "Type", "MTD", "Order", 1 ),
    ADDCOLUMNS ( DATESQTD ( 'Date'[FundedDate] ), "Type", "QTD", "Order", 2 ),
    ADDCOLUMNS ( DATESYTD ( 'Date'[FundedDate] ), "Type", "YTD", "Order", 3 ),
    ADDCOLUMNS (
        PREVIOUSMONTH ( DATESMTD ( 'Date'[FundedDate] ) ),
        "Type", "Last Month",
        "Order", 4
    ),
    ADDCOLUMNS (
        PREVIOUSQUARTER ( DATESQTD ( 'Date'[FundedDate] ) ),
        "Type", "Last QTR",
        "Order", 5
    ),
    ADDCOLUMNS (
        PREVIOUSYEAR ( DATESYTD ( 'Date'[FundedDate] ) ),
        "Type", "Last Year",
        "Order", 6
    ),
    ADDCOLUMNS (
        DATESINPERIOD ( 'Date'[FundedDate], TODAY () - 30, 30, DAY ),
        "Type", "Last 30 Days",
        "Order", 7
    ),
    ADDCOLUMNS (
        DATESINPERIOD ( 'Date'[FundedDate], TODAY () - 90, 90, DAY ),
        "Type", "Last 90 Days",
        "Order", 8
    ),
    ADDCOLUMNS (
        DATESINPERIOD ( 'Date'[FundedDate], EOMONTH ( TODAY (), -1 ), -13, MONTH ),
        "Type", "Lookback 1 Year",
        "Order", 9
    ),
    ADDCOLUMNS (
        CALENDAR ( MIN ( 'Date'[FundedDate] ), MAX ( 'Date'[FundedDate] ) ),
        "Type", "Custom",
        "Order", 10
    ),
    ADDCOLUMNS (
        DATESINPERIOD ( 'Date'[FundedDate], EOMONTH ( TODAY (), -1 ), -3, MONTH ),
        "Type", "Last 3 Months",
        "Order", 11
    ),
    ADDCOLUMNS (
        DATESINPERIOD ( 'Date'[FundedDate], EOMONTH ( TODAY (), -1 ), -9, MONTH ),
        "Type", "Last 9 Months",
        "Order", 12
    )
)

Get the correct result:

I attach my sample below for your reference.

Best regards,

Community Support Team_yanjiang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mclawler]

Now I'm getting this error upon creating the Date Periods table, it won't allow me to:

[mclawler]

I thnk that would normally work, but unfortunately we don't fund a loan every single day and sometimes we do multiple per day, so PBI won't allow me to mark the table as a Date Table:

[v-yanjiang-msft]

Hi @mclawler ,

Please see my attached pbix, it also works even though don't mark as date table.

Best regards,

Community Support Team_yanjiang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@mclawler , You should always use date table marked as date table in such cases. No .Date

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5b...

refer

https://medium.com/chandakamit/power-bi-when-i-felt-lazy-and-i-needed-too-many-measures-ed8de20d9f79