Forum Discussion

hanyfouda2023's avatar
hanyfouda2023
Icon for Helper II rankHelper II
2 years ago

Convert monthly basis to daily basis

Dear helpers, I have budget based on monthly basis need to convert it to daily basis to check daily and MTD budget achivmement I used below measures Daily Target = CLOSINGBALANCEMONTH(sum(Budget2...
Need Help
Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi hanyfouda2023 ,

Based on the DAX expression you provided, By using ‘Daily Target’, monthly budget data can be successfully converted to daily budget data.But 'TOTALMTD(count('Date'[Date]),'Date'[Date])' in ‘MTD_ Target’ by default returns a value of 10. It defaults to calculating a full month's worth of data.I checked the data in your table 'Budget2024'. MTD cannot be calculated in the absence of data accurate to the day. Ensure that a new date column ’Date’ is added to store data accurate to the day. Then use the following DAX expression:

MTD_Target = TOTALMTD(SUM('Budget2024'[Daily Sales]),'Budget2024'[Date])

Best Regards,
Wenbin Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

hanyfouda2023's avatar
hanyfouda2023
Icon for Helper II rankHelper II
2 years ago

Thanks v-zhouwen-msft,

I need to understand below sentence with more clarfication

"Ensure that a new date column ’Date’ is added to store data accurate to the day"