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 of the problems I have noticed is that if we choose the month of February then the formula that will display the previous month (January) will display the January calculation only until the 28th day and not until the 31st of January.

I would love any help!

Attached is PBIX file and DATA

 

https://1drv.ms/f/s!AonyYI-TdspHgUgReR5uqvnKLTCF

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
     
     
     

6 Replies

  • 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)))

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi, netanel ;

    I haved open your pbix but I'm not sure what you want the output to be and the logic, maybe change the DATESMTD.Hope to share more details.

    Best Regards,
    Community Support Team_ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.