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

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

    • 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

  • juju's avatar
    juju
    Helper III
    9 years ago

    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.