MTD Calculation

[juju]

I am using the formula below to pull the MTD value from the previous day.  I'd like to make the value of the first day of each month blank - so it doesnt pull from the last day of the previous month. I setup a calendar table and tried a couple of filters but none works. Will appreciate some help. 

        Previous Day MTDCalc = 
        TOTALMTD (
            CALCULATE (
                CALCULATE ( 
                    SUM(FactTable[Value]),
                    FILTER (
                        FactTable,
                        FactTable[Channel]= "MyChannel"
                    )
               )
            ),
            PreviousDay (FactTable[Date])
        )

[dkay84_PowerBI]

I'm confused though. Why would your MTD calc be pulling in data from the previous month?

[juju]

I am looking to pull the value of the previous days MTD calculation. But the code I posted pulls the value of the last day of the previous month for the first day of the month .

I tried the flag you suggested using a DAY (DateDim[Date]) . The if statement didn't work when I set it to check for value = 1 .  Field just came up blank.  

[dkay84_PowerBI]

I will try to play around with this tomorrow but can you confirm that you have a day slicer to choose a day and you want to return a MTD value up to the day prior to the one selected (unless day of month = 1).

[juju · Accepted solution]

All set. Managed to get it working with this :

Previous Day MTDcALC = 
IF ( FactTable[Date] = STARTOFMONTH(FactTable[Date]), BLANK(), 
    TOTALMTD (
        CALCULATE (
            CALCULATE ( 
                SUM(FactTable[Value]),
                FILTER (
                    FactTable,
                    FactTable[Channel]= "MyChannel"
                )
           )
        ),
            PreviousDay (FactTable[Date])
        )
    )

Thxxxx

[v-huizhn-msft]

Hi @juju,

I am very glad to hear you have resolved your problem. Please mark the corresponding replay as answer, which will help other people find solution easily and clearly.

Best Regards,
Angelia

[juju]

Yes - I am trying to calculate it - without using the slicer. But I guess it's th same calculation if I added a day slicer to the report. 

[dkay84_PowerBI]

Could you add a calc column for "first day of month" flag and then use an IF statement to get what you want? Not sure based on your description but pseudo code would be:

If "flag" is true then start MTD calculation from 0 else MTD calculation