New Measure Dependent on Month

[Dbuck44]

I have a Sales Forecast that will measure against Budget. Budgeted amounts are already available into the future months. Actual Sales, however, have occurred yet.  I want 2 new measures.  SALES YTD and BUDGET YTD depending on what month we're in.  I have a column I created providing current month as integer based on a date.time function.   For example, if it were JUNE 2023.   I want to see the SUM of prior months of the year "Sales Actual" and "Budget Amount" in sepate measures only for January thru May. Ignore all else.  June thru December hasn't happened yet.  If we're in October, I want the measure to show totals from Jan-Sep.  And so on. Without any user intervention.  Can this be done.

[KNP]

The measures suggested won't work without a date table, marked as a date table as they use time intelligence functions.

If you can, you should always include dimension tables to get the most benefit from Power BI.

If you don't have that option then please post some sample data and @Mikelytics or I will offer a solution. 

[Dbuck44]

Power Query editor doesn't accept the TODAY() function.  I've created a new custome column called CurrentMthNum using:     Date.Month(Date.Time.LocalNow()))   

[KNP]

@Mikelytics was referring to a 'New Measure' which is the more appropriate place to create what you're trying to do. It doesn't need to be a column, it can be calculated on the fly.

Is there any reason why you want to do it in Power Query (Transform data)?

See screenshot below.

[Dbuck44]

I'm not using a date dimension table. I have values entered in main table by Month. Month is the lowest level date parameter.  I have MthNum for the respective month or ActualSales and BudgetedSales.  I have another column that is calculated the provides the CurrentMthNum , which as of right now is 11.

So I want to calculate the Sum of Budegted Sales as a new aggregate measure, but only for Months incurred, in this case Months 1 through 10 or Where [CurrentMthNum] < Max([CurrentMthNum])

[Dbuck44]

Meant "Create Custom Column"

[Mikelytics]

HI @Dbuck44 

Please try the following:

for [Measure] you have to put in your calculation e.g. SUM(table[value]) or your base measures (SALES YTD or BUDGET YTD ) and you should have a date dimension table.

Time Intelligence | RunningYTD = 

var var_TodayMonth = MONTH(TODAY())
var var_TodayYear = Year(TODAY())
var var_EndOfLastMonth = DATE(var_TodayYear,var_TodayMonth,1) - 1

RETURN
CALCULATE(
    CALCULATE(
        [Measure],
         DATESYTD ('Dim Date'[Date])
    ),
    'Dim Date'[Date] = var_EndOfLastMonth
 )

or

Time Intelligence | RunningYTD = 

var var_TodayMonth = MONTH(TODAY())
var var_TodayYear = Year(TODAY())
var var_EndOfLastMonth = DATE(var_TodayYear,var_TodayMonth,1) - 1

RETURN
CALCULATE(
    TOTALYTD,
        [Measure],
        'Dim Date'[Date]
    ),
    'Dim Date'[Date] = var_EndOfLastMonth
 )

Best regards

Michael

-----------------------------------------------------

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.

-----------------------------------------------------

LinkedIn

[Dbuck44]

OK, I've created the new measure using this code.  I'm getting Syntax errors.

Whick of these lines requires me to change it to my application's actual Field Names.

It's not accepting the Dim Date'[Date]

Do I changed the [Measure] component of the code to my actual field called [Actual] ?

[KNP]

Yes, should be easy.

Can you paste some sample data please (not a screenshot).

This will make it easier to come up with an answer for you.

[Dbuck44]

Where does that time intelligence code go?  I'm using the Transform Data power query editor and "create custome column" to create the measure

[Mikelytics · Accepted solution]

Hi @Dbuck44 ,

Even if you do not have it now I highly recommend you to start using date tables since it will help you now as well as in future models since it is a highly recommended and best practice. So please adjust your current model. Here is how you can do it:

1) go to your table in Power Query and create a custom column called "Date" with the following formula: #date([column of year],[column of month],1). By that the first of the month represents your month in a year.

2) Now please create a date table with at least the following sequence 01.01.20xx, 01.02.20xx ... 01.12.20xx - You can also use a complete date table - it doesnt matter in this case.

3) connect your new date table to your transaction table to have a proper star schema

4) use my measure above 🙂

Best regards

Michael

-----------------------------------------------------

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.

-----------------------------------------------------

LinkedIn