Please help whit measure

[netanel]

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

[v-yalanwu-msft]

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.

[amitchandak]

@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]

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]

@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]

@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 · Accepted solution]

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