TAKE A CURRENT PROPORTIONAL OF THE DAYS ELAPSED OF THE MONTH

[Syndicate_Admin]

Hi, I need to generate a report, which is used daily with the data corresponding to the sales of the previous day. The structure and logic would be something like this:

I have different branches, each branch has a sales target in the month. The table has a structure similar to this [id_sucursal, period, target] where the period is, for example, 52023 (May 2023).

I also have a sales table of the entire company in general, where the sales of all branches are located.

My doubt is how to get how much time elapsed of the month proportionally, that is, a month has 30 days, if 15 days elapsed, I spend 50% of the month.

I need this information since the detail of the fulfillment of the objective is proportional to the month. Continuing with the example, if a branch has a sales goal of $ 100,000 and on the 15th day sold $ 50,000, compliance to date is 100%, since after 50% of the month, I achieve 50% of the objective.

So my doubt is how I could get said proportional of the month, to be able to perform the analysis.

Thanks a lot.

Best regards

[v-yueyunzh-msft · Accepted solution]

Hi , @Syndicate_Admin 

According to your description, you want to get the percentage by the month of days has gone.

I have no sample data so i test this in my side, you can refer to , i hope it can help you .

This is my test data:

We can create a measure like this:

Measure = var _cur_date =EOMONTH( MAX('Target'[period]),-1)+1
var _month_end =  EOMONTH(_cur_date,0)
var _su = MAX('Target'[id_sucursal])
var _sales =SUMX( FILTER('Sales' , 'Sales'[Date]>=_cur_date && 'Sales'[Date]<= _month_end&& 'Sales'[id_sucursal]=_su),[Sales] )
var _today_day =DAY( TODAY())

return
IF( YEAR(_cur_date) = YEAR(TODAY()) && MONTH(_cur_date)=MONTH(TODAY()),DIVIDE( _sales,  DIVIDE(_today_day,DAY(_month_end))*SUM(Target[target]) ) , DIVIDE(_sales,SUM(Target[target]))     )

Then we can get this:

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem. (You can also upload you sample .pbix [without sensitive data] to the OneDrive and share with the OneDrive link to me ! )

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

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

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523