Run Rate based on the targets of the selected month

[Anonymous]

Hello guys,
In Power BI i have a source file with targets for each month, for example:
Jan 2022 - 5000
Feb 2022 - 3000
Mar 2022 - 2500
and so on..

I need to have a clustered column chart, with the following:

• Actual Revenue ( from the date range selected and brand selected) - DONE
• Cumulative Target ( from the date range and brand selected) - DONE
• Run Rate with the cumulative target for the last month that appears in the data range. - NOT DONE

Basically if I have a target of 20.000$ for the last month that I have selected(March-2022) (01-01-2022 - 15-03-2022),
i need to create a measure that splits that amount by the total number of days of that specific month and then
multiplies by the actual days to get the Run Rate for that month.

Let me know if I was clear. I will share with you just a dummy excel file with some targets

SAMPLE 

If you are about to share a possible solution, just stick to the Run Rate, for the others I already have a solution

Thanks guys

Diego

[v-chenwuz-msft · Accepted solution]

Hi @Anonymous ,

Before this , you need a table named Calendar. Maybe you can try some measure like the following code:

Measur =
VAR _Target = 20000 // replace by [your measuer]
VAR _Actualday = 25 // replace by [your measuer]
VAR _seleteddate_start =
    MIN ( 'Calendar'[Date] )
VAR _seleteddate_end =
    MAX ( 'Calendar'[Date] )
VAR _Diffday =
    DATEDIFF ( _seleteddate_start, _seleteddate_end, DAY )
VAR _ActualAmount =
    SUMX (
        FILTER ( 'Table', [Month] <= _seleteddate_end && [Month] >= _seleteddate_start ), // [Month] is date type, or you can use DATE([year],[month],1) as substitutes.
        [Amount]
    )
RETURN
    DIVIDE ( _Target / _Diffday, _ActualAmount / _Actualday )

You should modify this code to suit your model.

Best Regards

Community Support Team _ chenwu zhu

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

[lbendlin]

If you provide a better date field (for example first day of each month) and link it to your Calendar table  you can use the standard cumulative time intelligence functions like TOTALYTD  etc.

If you want to use a shortcut you can add a month number column to your data

= Table.ReplaceValue(Table.AddIndexColumn(Source, "MonthNo", 0, 1, Int64.Type),each [MonthNo],each 1+Number.Mod([MonthNo],12),Replacer.ReplaceValue,{"MonthNo"})