topic Re: Converting monthly value into daily DAX in DAX Commands and Tips

Converting monthly value into daily DAX

Anonymous — Sun, 12 Mar 2023 08:50:37 GMT

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

Re: Converting monthly value into daily DAX

tamerj1 — Sun, 12 Mar 2023 09:17:20 GMT

Hi Anonymous

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

Re: Converting monthly value into daily DAX

Anonymous — Mon, 13 Mar 2023 06:27:55 GMT

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

Re: Converting monthly value into daily DAX

tamerj1 — Mon, 13 Mar 2023 06:44:28 GMT

Anonymous
Please try

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

Re: Converting monthly value into daily DAX

Anonymous — Mon, 13 Mar 2023 06:51:55 GMT

tamerj1

still returns only one value

Re: Converting monthly value into daily DAX

tamerj1 — Mon, 13 Mar 2023 07:30:16 GMT

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.

Re: Converting monthly value into daily DAX

Anonymous — Mon, 13 Mar 2023 07:45:35 GMT

tamerj1 ,
you are a genius!

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

Re: Converting monthly value into daily DAX

tamerj1 — Mon, 13 Mar 2023 07:54:09 GMT

Anonymous

It was very clear