Forum Discussion

juju's avatar
juju
Helper III
9 years ago
Solved

MTD Calculation

  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 mon...
Need Help
  • juju's avatar
    juju
    9 years ago

    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

dkay84_PowerBI's avatar
dkay84_PowerBI
Microsoft Employee
9 years ago
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's avatar
juju
Helper III
9 years ago

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's avatar
    v-huizhn-msft
    Microsoft Employee
    9 years ago

    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