Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Dbuck44
Frequent Visitor

New Measure Dependent on Month

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.

1 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

 

 

 

------------------------------------------------------------------
Visit my blog datenhungrig which I recently started with content about business intelligence and Power BI in German and English or follow me on LinkedIn!

View solution in original post

10 REPLIES 10
KNP
Super User
Super User

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. 

 

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x
Dbuck44
Frequent Visitor

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

@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.

 

KNP_0-1669237616067.png

 

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x
Dbuck44
Frequent Visitor

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
Frequent Visitor

Meant "Create Custom Column"

Mikelytics
Resident Rockstar
Resident Rockstar

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

 

------------------------------------------------------------------
Visit my blog datenhungrig which I recently started with content about business intelligence and Power BI in German and English or follow me on LinkedIn!

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
Super User
Super User

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.

 

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x
Dbuck44
Frequent Visitor

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

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

 

 

 

------------------------------------------------------------------
Visit my blog datenhungrig which I recently started with content about business intelligence and Power BI in German and English or follow me on LinkedIn!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.