Total amount by day

[ROG]

Hi all,

I have the amount by month, but I need to have the amount by week too. So I thought creating a measure that gives me the daily amount so I could have a line chart showing the amount by month with the ability to drill down by weeks.

Can you help me with a dax measure that gives the total amount by day?

You can see the something is wrong with my measure as I'm the values by the 1st week of each month only.

Daily Amount =

VAR _Day_In_Context =

COUNTROWS( 'Calendar' )

VAR _Days_In_Month =

CALCULATE( COUNTROWS( 'Calendar' ) ,

    ALL( 'Calendar' ) ,

    VALUES( 'Calendar'[Month Year] ) )

VAR _Total_Amounts =

CALCULATE( SUM( Amount_2022[Budget] ) ,

    TREATAS( VALUES( 'Calendar'[Month Name Long] ) , Amount_2022[Month] ) )

VAR _Daily_Amount =

IF( HASONEVALUE( 'Calendar'[Date] ) ,

    DIVIDE( _Day_In_Context , _Days_In_Month) * _Total_Amounts ,

    _Total_Amount )

VAR _Results =

SWITCH( TRUE() ,

    ISINSCOPE( 'Country'[Country Name] ) , BLANK() ,  

    ISINSCOPE( 'Customer Category'[Customer Category Name] ) , _Daily_Amount,

_Daily_Amount )

RETURN

_Results

Thanks!

[amitchandak]

@ROG , refer if my blog can help

Distributing/Allocating the Monthly Target(Convert to Daily Target): Measure ( Daily/MTD): https://community.powerbi.com/t5/Community-Blog/Power-BI-Distributing-Allocating-the-Monthly-Target-...

[ROG]

Thansk @amitchandak 

I've tried this, but it's not shwoing any values by days, only by the 1st day of the month. Why is that?

Date comes from my Calendar table and below is the budget table.

Below is my model.

Daily BudgetChurn = CLOSINGBALANCEMONTH(sum(Budget_Churn_2022[Budget Churn])/(maxx(Budget_Churn_2022,DATEDIFF(Budget_Churn_2022[MonthStart],Budget_Churn_2022[MonthEnd],day))+1),'🔎Calendar'[Date])

MTD BudgetChurn = CLOSINGBALANCEMONTH(sum(Budget_Churn_2022[Budget Churn])/(maxx(Budget_Churn_2022,DATEDIFF(Budget_Churn_2022[MonthStart],Budget_Churn_2022[MonthEnd],day))+1),'🔎Calendar'[Date]) *TOTALMTD(COUNT('🔎Calendar'[Date]),'🔎Calendar'[Date])

Thank you so much!