topic Re: Monthly to daily conversion in DAX Commands and Tips

Monthly to daily conversion

Anonymous — Wed, 11 Jan 2023 09:09:26 GMT

Hello i have monthly data need to convert it into daily
for eg. for dec 22 my monthly data is 1814 in daily i want 1814/31(no. of days in month) = 59
Thanks in advance

Re: Monthly to daily conversion

Camel — Wed, 11 Jan 2023 09:36:27 GMT

Hi Anonymous
Create a calculate column like below
col1=int(TABLE["Monthly"]/31)

the above will give you the same day itself, if you want the decimal to be the next number you can use the following
col2=
var divi=TABLE["Monthly"]/31
return

round(divi,0)

Best regards
Camel
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Appreciate your thumbs up!
@ me in replies or I'll lose your thread.

Re: Monthly to daily conversion

Anonymous — Wed, 11 Jan 2023 10:35:49 GMT

Thank you Camel for your reply but i want this data to change as per month selection
e.g. for November 22 number days are 30 so it should change accordingly

Re: Monthly to daily conversion

Camel — Wed, 11 Jan 2023 10:47:55 GMT

In the above shared excel there is no month data ,if you could help me with the exact context and requirement i could help you.
If there is a date column in your dataset can you share that data along with existing data

Re: Monthly to daily conversion

Anonymous — Wed, 11 Jan 2023 12:35:17 GMT

Hi Camel sharing dummy data and expected results with you please check

https://docs.google.com/spreadsheets/d/1PydFWRHcYA62FKEoyqCo3i49JzqJCO2o/edit?usp=share_link&ouid=105643774708082133910&rtpof=true&sd=true

Re: Monthly to daily conversion

Anonymous — Thu, 12 Jan 2023 05:33:53 GMT

are you able to access the file ?

Re: Monthly to daily conversion

FreemanZ — Thu, 12 Jan 2023 05:56:33 GMT

hi Anonymous

The URL you shared need password to access. Could you paste some table as text in the reply?

p.s. please consider @someone, ifyou seek further suggestion.

Re: Monthly to daily conversion

Camel — Thu, 12 Jan 2023 07:42:57 GMT

Hi Anonymous i think you can substitue 31 with a DAYS(EOMONTH(MONTH(NOW()),0),0) in var col1 and it will work for you if you are looking for current month count. Im not able to access the link you shared btw.

col1=
var d1=DAYS(EOMONTH(MONTH(NOW()),0),0)
var d2=TABLE["Monthly"]/d1

return round(d2,0)

Re: Monthly to daily conversion

Anonymous — Tue, 31 Jan 2023 07:46:48 GMT

Hi Anonymous ,

According to your description, here are my steps you can follow as a solution.

(1) This is my test data.

(2)We can create a table.

CALENDAR = CALENDAR(DATE(2018,1,1),DATE(2023,12,31))

(2) We can create a measure.

Result = VAR _day = DAY ( EOMONTH ( DATE ( MAX ( 'CALENDAR'[Date].[Year] ), MONTH ( CONVERT ( MAX ( 'CALENDAR'[Date].[Month] ) & " 1", DATETIME ) ), 1 ), 0 ) ) RETURN DIVIDE ( SUM ( 'Table'[Monthly] ), _day )

(3) Then the result is as follows.

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

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