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'm confused though. Why would your MTD calc be pulling in data from the previous month?
  • juju's avatar
    juju
    Helper III
    9 years ago

    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'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