Custom Formulas

[Anonymous]

Hello, 

I have a Value that I need to normalize to 30 days per month. I already have a date table set up with the days in each month. I have been trying to creat a new colume the will essentially do this:

"Value"/"Daysinmonth" * 30

I have tried several different ways and can't seem to get it right. Any suggestions?

[v-yiruan-msft]

Hi @Anonymous ,

Would you like to calculate the average value of each month , but the average calculated based on 30 days instead of the actual number of days in a month ? I'm sorry I'm not clear about your requirement... Can you provide some sample data and the results you expect ? Thank you.

Best Regards

Rena

[amitchandak]

Please try

Column in table = (table[Value]/day(eomonth(Table[date]))*30)
measure = sum(table[value])/maxx('Date',day(ENDOFMONTH('Date'[date])))*30
//In case you areb noth viewing by month year
sumx(summarize(table,Date[Month Year],"_sum",[measure]),[_sum])

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Datanaut Connect on Linkedin