Forum Discussion

netanel's avatar
netanel
Icon for Post Prodigy rankPost Prodigy
5 years ago
Solved

Please help whit measure

Hello everyone, I try to calculate a daily average, a daily average last month, the percentage change from month to month, and the change in money from month to month. (Also for budget table) One o...
Need Help
  • netanel's avatar
    netanel
    5 years ago

    I found the right formulas Thanks!
    these are: 

    Diff$ Budget V =
    Var _MTDA = CALCULATE([MTD Sales],Sheet1[Data Surce]="A")
    return
    _MTDA-[MTD Sales for Budget 2021 V]
     
    Net USD % difference Budget V =
    Var _MTDA = CALCULATE([MTD Sales],Sheet1[Data Surce]="A")
    return
    (_MTDA-[MTD Sales for Budget 2021 V])/_MTDA
     
     
     
netanel's avatar
netanel
Icon for Post Prodigy rankPost Prodigy
5 years ago

Thanks, amitchandak 
But I'm guessing you did not enter my files and database.

I am looking for AVG DAILY and Last Month AVG average
2. I know how to do all the formulas great, the problem is that the numbers do not come out correctly, probably because of my database or another problem

Anyway thanks for the experience

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

netanel , Assume you have measure sales and you want Daily Avg for month and prior month

 

MTD Sales = CALCULATE(averagex(Values('Date'[Date]), [Sales]) ,DATESMTD('Date'[Date]) )

 

same way

last MTD Sales = CALCULATE(averagex(Values('Date'[Date]), [Sales]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

  • netanel's avatar
    netanel
    Icon for Post Prodigy rankPost Prodigy
    5 years ago

    amitchandak 

     

    Please see in the attached picture,
    I changed to your formulas and still the budget table is wrong (marked in yellow) by the way my formulas was also good before just did not work in the budget table
    Can you also look at the PBIX file I shared and my database Please

     

    • netanel's avatar
      netanel
      Icon for Post Prodigy rankPost Prodigy
      5 years ago

      I found the right formulas Thanks!
      these are: 

      Diff$ Budget V =
      Var _MTDA = CALCULATE([MTD Sales],Sheet1[Data Surce]="A")
      return
      _MTDA-[MTD Sales for Budget 2021 V]
       
      Net USD % difference Budget V =
      Var _MTDA = CALCULATE([MTD Sales],Sheet1[Data Surce]="A")
      return
      (_MTDA-[MTD Sales for Budget 2021 V])/_MTDA