Converting monthly value into daily DAX

[Anonymous]

hi everyone!

please help with the following,

I'm trying to convert montly target value for sales into daily average with respect to number of days in a given month.

this is my data model

 tables are connected through calendar table, 
Actual table has data on daily level, but Plan table has data on monthly level

 and I need to see it in the following format

important thing to concider is days in each month

[tamerj1]

Hi @Anonymous 

why did you divide the march planned amount over 46.5 days? 331,962/46.5 = 7,139?

[Anonymous]

hi @tamerj1 ,
sorry, my bad I captured more days for march,
daily figure should be 10 708

[tamerj1 · Accepted solution]

@Anonymous 
Please try

Planned Sales Daily =
DIVIDE (
    [Planned Sales],
    COUNTROWS (
        CALCULATETABLE (
            VALUES ( 'Calendar'[Date] ),
            REMOVEFILTERS (),
            VALUES ( 'Calendar'[Year] ),
            VALUES ( 'Calendar'[Month] )
        )
    )
)

[Anonymous]

@tamerj1 

still returns only one value 

[tamerj1]

@Anonymous 
This is a relationship cardinality issue.
You need to create the relationship between 'Date' and 'Planned' at month level not at day level. You can create a YearMonth column like FORMAT ( Planned[Date], "YYYYMM" ) in the Planned table and FORMAT ( 'Date'[Date], "YYYYMM" ) in the 'Date" table and connect both columns together Many-Many single direction.

[Anonymous]

@tamerj1 ,
you are a genius!

thanks, it works now, but how did you figured it out ? 

[tamerj1]

@Anonymous 

It was very clear