Forum Discussion
New Measure Dependent on Month
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.
-----------------------------------------------------
Super UserMikelytics 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.
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])