Prior MTD by Day DAX Calc

[andrew5083]

Hello,

My company recently switched to Power BI for our presentation layer.  I've been really struggling to master DAX time intelligence functions as our last BI solution had very intuitive, dynamic and straightforward time intelligence.

I've managed to re-create most of our commonly used time intelligence measures for an analysis, but am struggling with getting Prior MTD Totals by Day.  

This should not be an accumulated total and should not show dates past the current day in visuals/matrices.

These are very common measures my company uses together frequently in KPIs and reports.  The closest I've gotten to it are the measures below:

Daily Subtotal

Daily Subtotal:= 
    IF(
        SELECTEDVALUE('Date Invoice'[Full Date]
        ) 
    <= TODAY(),
       [Subtotal]
    )

 Prior Month Daily Subtotal

PM Daily Subtotal:= 
    IF(
        SELECTEDVALUE('Date Invoice'[Full Date]
        )
     <= TODAY(),
        CALCULATE([Daily Subtotal], 
            DATEADD(
            'Date Invoice'[Full Date], -1, MONTH
            )
        )
    )

The issue I'm having is the total displayed at the bottom of the PM Daily Subtotal column.  That is the total for the full month of April 2021, despite the visible numbers in the column adding up to $8,072,834.20.  I am assuming the 28-31 of May are not being used in this calc, otherwise those dates should be present in the data in the matrix?

The screenshot below is using test data.

Any advice on how to get the total for that column to line up with the data being shown?

[CNENFRNL]

[andrew5083]

@CNENFRNL thank you for the suggestion.  Sorry for the hand holding, but how should I reference this calculated column in a measure?

[amitchandak]

@andrew5083 , I think you want force MTD below today

example

MTD QTY forced=
var _max = today()
return
if(max('Date'[Date])<=_max, calculate(Sum('order'[Qty]),DATESMTD('Date'[Date])), blank())
//or
//calculate(Sum('order'[Qty]),DATESMTD('Date'[Date]),filter('Date','Date'[Date]<=_max))
//calculate(TOTALMTD(Sum('order'[Qty]),'Date'[Date]),filter('Date','Date'[Date]<=_max))

LMTD QTY forced=
var _max = date(year(today()),month(today())-1,day(today()))
return
if(max('Date'[Date])<=_max, CALCULATE(Sum('order'[Qty]),DATESMTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max), blank())
//OR
//CALCULATE(Sum('order'[Qty]),DATESMTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max)
//TOTALMTD(Sum('order'[Qty]),dateadd('Date'[Date],-1,year),'Date'[Date]<=_max)

or

Month behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Month))

[andrew5083]

@amitchandak thank you for your reply.

I tested the functions you posted.  Unfortunately these are giving me a accumulating month total.  I've created dax statements that capture this data as shown in the first two columns (MTD Accum & PMTD Accum) in the screenshot I posted.  The final total in your test column is correct.  If I can get the daily subtotal instead of an accumulating subtotal then it would work.

What I'm trying to get is a Prior MTD Daily Total, as shown in the last column of the original screenshot, but without the full month's total being referenced in the bottom total line of the screenshot.

I also attempted to adapt the forced date logic in your solutions to an existing test column.  This gave me the same result as the PM Daily Subtotal column I created.  The amounts posted for each day are accurate, but the total referenced at the bottom is not.

Could this be a bug?  We're literally forcing the date to filter to today in multiple areas in our solutions.