Forum Discussion

netanel's avatar
netanel
Icon for Post Prodigy rankPost Prodigy
4 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
    4 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
     
     
     
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
4 years ago

netanel , With help from till intelligence

 

Measure till date  - this month , Last month

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

 

full data

this month = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH('Date'[Date])))

 

last MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))

 

previous month value = CALCULATE(sum('Table'[total hours value]),previousmonth('Date'[Date]))

 

Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKA

 

netanel's avatar
netanel
Icon for Post Prodigy rankPost Prodigy
4 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
    4 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
      4 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
        4 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