Monthly budget to daily budget

[MaximeVall]

Hello,

I want to convert a monthly budget to a daily budget to be in coherence with the filter segment : 

Here, you can see that the "B2021" column (the monthly budget) is good because the filter select a entire month.

But when I select a part of the month for example, the "B2021" column don't adapt to the filter because I have got a monthly budget and it return the monthly budget even for a part of the month.

So what I want is a budget that there is in accord with the filter segment. I don't know how to do that, I have tried to look at some others posts on the forum but I can't do it. Even if, I look the tutorials of @amitchandak I don't understand how to proceed.

Here is the my table "budget" : 

If somebody could help me, It would be appreciate. I can provide my powerbi file, or more detail if you want

Sincerly, a trainee

[MaximeVall]

Hello everybody,

Let me explain :

Here is an extract of my database : 

I have got a budget for each section for each month of the 2021 year. The format of my date is (dd/mm/yyyy).

I have created a table that tell me the budget (column B2021) per section according to the date period selected via the segment filter :

The problem is that even if I select a part of the month like here (01/01/2021 to 21/03/2021) it return the budget of january/february/march. Or I just want the budget to the 21th march (to the date select in the segment filter).

So I have tried a new measure that you can see in the column B2021 ajusted : 

% avancement du mois = % progress of the month

But this is not right because what it returns is that : 

(budget january + budget february + budget march) - (budget january + budget february + budget march) * (1 - % progress of the month)

What I want is a formula that return me that :

(budget january + budget february + budget march) - (budget of march) * (1 - % progress of the month)

Here I use name of the month to explain more clearly of what I expect to have. But the formula need to be in coherence with the date selected by the filter segment. 

I don't know if it possible to use a formula like "CURRENTMONTHSELECT" or something like this.

I don't know if I'm making myself clear, but help would be welcome .

Sincerly.

[v-janeyg-msft]

Hi, @MaximeVall 

The file you shared seems to be unavailable. From your description, although I know your question, I don’t know whether B2021 is a column or a measure and its code. Can you share some sample fake data(in table format) and your desired result logic? So we can help you soon.

Best Regards

Janey Guo

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

[MaximeVall]

Hi @v-janeyg-msft ,

Sorry, the link to the file was only available during 24 hours.

Here is a new link (24h) : https://www.partage-temporaire.fr/2021/05/27/reporting-test-2/

B2021 is a column : 

 Here is my desired result but if it is not exact it's not a problem: 

I don't know if you my file .pbix it can be OK for you ? Or If I really need to send you a sample fake data, tell me. 

Sincerly

[v-janeyg-msft]

Hi, @MaximeVall 

I cheked your shared file. In your budget table, the column only have one day in a month, so no matter how you filter the date in a month, the result will only change once. You said that the result is incorrect, so how should the data change in the middle of the month?

Best Regards

Janey Guo

[MaximeVall]

Yeah, what I want is that the budget need to be in the same proportion of the progress of the month.

For example with : 

Here the date selected is : 01/01/2021 to 22/03/2021 and the % progress of the month is at 70% so the desired budget result need to be : January + February + 70% of March (adapted to the current % progress of the month).

Actually I have that : 

And I expect to have that : 

I think, we need to create a new table daily budget, where I have the budget of each section for each day of the month. 

By dividing the monthly budget by the number of days in the month.

I don't know if I explain well.

[v-janeyg-msft · Accepted solution]

Hi, @MaximeVall 

I get it, and you need to create a measure instead of column in table.

Like this:

Measure =
SUMX (
    FILTER (
        RELATEDTABLE ( budget ),
        [début mois]
            < DATE ( YEAR ( MAX ( 'Date'[Date] ) ), MONTH ( MAX ( 'Date'[Date] ) ), 1 )
    ),
    [budget]
)
    + SUMX (
        FILTER (
            RELATEDTABLE ( budget ),
            [début mois]
                = DATE ( YEAR ( MAX ( 'Date'[Date] ) ), MONTH ( MAX ( 'Date'[Date] ) ), 1 )
        ),
        [budget]
    ) * [% avancement du mois]

Best Regards

Janey Guo

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

[MaximeVall]

@v-janeyg-msft 

It works well ! 

A big thanks to you ! You have made my day ! 

I am really happy ! thanks for the time passed with me !

Good continuation !

[MaximeVall]

Hi @amitchandak , 

I'm really sorry to bother you but are you still willing to help me? I can't seem to get my way.

[amitchandak]

@MaximeVall , Can you paste data in table format here? I need to know the output you want. seem like we need to distribute data into a new table.

Attached is my file on my data. You can upload to one drive or dropbox and share a link

[MaximeVall]

Hi @amitchandak , I don't know if I reply correctly for you, I don't understand all. But tell me If you need some details or something.

[MaximeVall]

Here is a link to my .pbix :

https://www.partage-temporaire.fr/2021/05/25/reporting-test-2/

I can't open your pbix. because I haven't got the lastest version of Power BI and I am in my company where I can't actually do the lastest update...